数据模型用于映射两个”喜欢”

huangapple go评论65阅读模式
英文:

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 '!'

huangapple
  • 本文由 发表于 2023年7月12日 22:56:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76671938.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定