时间戳

首页 > 解决方案 > 如何使用 SQL 解析引用链接

sql - 如何使用 SQL 解析引用链接

问题描述

你好 StackOverflow 世界，

我有一个存储我所有网站会话数据的数据源，我希望分析推荐 URL 并将它们分为五类：主站点(www.mywebsite.com)、子站点(www.mywebsite.com/employees)、外部流量、谷歌、雅虎、必应、Facebook、Linkedin和Youtube。

为此，我必须解析引荐 URL。我已经部分弄清楚了，但是我当前的查询错误地将来自外部网站的 URL 分类，这些网站在其 URL 中包含我们的域。当我的查询运行时，它不会将这些 URL 分组为外部流量，而是创建自己的分组作为引荐 URL 的名称。

例如，使用此 URL，您可以看到我的站点名称是如何嵌入到其 URL 中的：

https://www.helpthepeople.com/redirect.action?link=https%3A%2F%2F**www.mywebsite.com**%2Femployers%2Fblog%2Fwhat-to-do-when-asking-for-help%2F&encoded=lFAJCUeGqgrDkdlYfDwwbEfCqGlV

我得到以下输出：

www.helpthepeople.com

期望的输出：

Outside Traffic

在大多数情况下，我的查询都在工作，但只有在上面的示例出现时我才会遇到问题。有人知道写这个的更好方法吗？我的查询如下：

SELECT 
    CASE 
        WHEN referrer_page LIKE '%mywebsite.com%' 
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%mywebsite.com/employees%' 
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'.com/',2),'/',1)
         WHEN referrer_page LIKE '%google%' 
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%yahoo%' 
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%bing%' 
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%facebook%'
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%linkedin%'
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)
        WHEN referrer_page LIKE '%youtube%'
            THEN SPLIT_PART(SPLIT_PART(referrer_page,'//',2),'/',1)  
        ELSE 'outside_referral_traffic' 
    END AS url_grouping,
    referrer_page,
    session_date,
    channel,
    medium,
    web_source,
    campaign_name,
    id, 
    COUNT (DISTINCT id) AS number_of_sessions
FROM biz_sessions
WHERE session_date >= '2019-07-01' AND session_date <= '2019-07-31'
GROUP BY 
    referrer_page,
    session_date,
    channel,
    medium,
    web_source,
    campaign_name,
    id

标签： sqldatabaseamazon-redshiftutmdbvisualizer

解决方案

在SQL SERVER使用replace,charindex和left

declare @string varchar(800) = 'https://www.helpthepeople.com/redirect.action?link=https%3A%2F%2Fwww.mywebsite.com%2Femployers%2Fblog%2Fwhat-to-do-when-asking-for-help%2F&encoded=lFAJCUeGqgrDkdlYfDwwbEfCqGlV'

select left(replace(replace(@string,'https://www.helpthepeople.com/redirect.action?link=',''),'https%3A%2F%2F',''),charindex('%2F',replace(replace(@string,'https://www.helpthepeople.com/redirect.action?link=',''),'https%3A%2F%2F',''))-1)

你可以很容易地把它翻译成MySQL

推荐阅读