英文:
Data model to map two "likes"
问题
我正在尝试找出如何建模一个用户管理的查找表，将呼叫类型映射到其他类型的网站访问。基本上，我想将与联系信息更改有关的客户服务呼叫关联到与联系信息更改相关的网页。我的初步想法是这样的。
这是SQL Server，如果有帮助的话 - Azure Synapse无服务器，但目前比概念更重要的是语法。
| id | call_topic | web_url |
|---|---|---|
| 1 | 联系信息 | site.com/contactinfo |
使用这个，我可以找到由同一个人进行的呼叫，使用以下方式连接：
calls.member_id = web_traffic.member_id AND (连接到我的查找表的一些内容)。
为了进一步扩展这个模型，如果我使用基本的"like"或者正则表达式，使用类似这样的查找表呢？(使用"like")
| id | call_topic | web_url |
|---|---|---|
| 1 | 联系% | site.com/contact% |
你可以将这看作是"任何以'联系'开头的呼叫主题和任何以'site.com/contact'开头的web_url"。但我不知道这个连接是什么样的。
英文:
I'm trying to figure out how to model a user-managed lookup table to map types of calls to other types of web visits. Basically I want to associate a call to customer service about contact info change to web pages related to contact info change. My initial thought is something like this.
This is SQL Server if that helps - Azure Synapse serverless, but syntax is less important at the moment than concept.
| id | call_topic | web_url |
|---|---|---|
| 1 | Contact Info | site.com/contactinfo |
Using this I can find calls by the same person using both
calls.member_id = web_traffic.member_id AND (some join to my lookup table).
To further extend this model, what if I use basic "like" or maybe regex, with a lookup table like this? (using "like")
| id | call_topic | web_url |
|---|---|---|
| 1 | Contact% | site.com/contact% |
You might read this as "any call topic that starts with 'Contact' and any web_url that starts with 'site.com/contact'". But I don't know what this join looks like.
答案1
得分: 1
如果您想要使用"like"操作符或正则表达式来执行与查找表的连接所需的模式匹配，您可以继续使用LIKE来匹配在查找表中定义的呼叫主题和web URL模式：
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic
AND web_traffic.web_url LIKE lookup_table.web_url
如果您想要进行更复杂的模式匹配，可以使用带有ESCAPE子句的LIKE操作符，并定义您的正则表达式模式：
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic ESCAPE '''
AND web_traffic.web_url LIKE lookup_table.web_url ESCAPE '''
英文:
If you want to use the "like" operator or regular expressions to perform pattern matching for joining your lookup table, you can proceed with LIKE to match the call topic and web URL patterns defined in the lookup table:
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic
AND web_traffic.web_url LIKE lookup_table.web_url
If you want to use regular expressions for more complex pattern matching, you can make use of the LIKE operator with the ESCAPE clause and define your regular expression patterns:
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic ESCAPE '!'
AND web_traffic.web_url LIKE lookup_table.web_url ESCAPE '!'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论