主页 / coding / 问题 / 77706362
Accepted
emi
Asked: 2023-12-23 09:23:16 +0800 CST

通过顶级竞争对手分析计算每日语音份额的 SQL 查询

  • 772

我需要有关编写查询来分析serp_analytics表中的网站流量数据的帮助。表结构如下：

柱子 类型
关键字 ID 整数
网站 varchar
est_traffic 整数
日期 约会时间

我的目标是计算我的网站 ('youtube.com') 的每日“声音份额”，并根据声音份额确定排名前 3 的竞争网站。分析应涵盖特定的日期范围，:start并提供:end参数。

具体要求：

  1. 计算每日语音份额：我需要计算“youtube.com”的每日语音份额。声音份额的计算方法是，将est_traffic某一天“youtube.com”的声音总和除以est_traffic当天所有网站的总声音量，再乘以 100。

  2. 确定前 3 个竞争对手：根据当天的声音份额:end，确定前 3 个网站（不包括“youtube.com”）。这些顶级竞争对手应包含在指定日期范围内每天的输出中。

  3. 输出结构：所需的输出是结构化格式，其中每个条目对应一个网站，包括“youtube.com”和前 3 个竞争对手。每个条目应列出该日期范围内的每日语音份额。如果网站在某一天没有数据，则其声音份额应显示为 0。

  4. 完整的日期覆盖范围：输出必须包括:start和 之间范围内的所有日期:end。如果某个网站在特定日期没有数据，则该日期的语音份额应为 0。

这是一个示例数据集：

| keyword id | website      | est_traffic | date       |
|------------|--------------|-------------|------------|
| 1          | google.com   | 10          | 2023-12-22 |
| 1          | facebook.com | 20          | 2023-12-22 |
| 1          | youtube.com  | 5           | 2023-12-22 |
| 1          | twitter.com  | 40          | 2023-12-22 |
| 1          | linkedin.com | 50          | 2023-12-22 |
| 1          | google.com   | 30          | 2023-12-23 |
| 1          | facebook.com | 20          | 2023-12-23 |
| 1          | youtube.com  | 5           | 2023-12-23 |
| 1          | twitter.com  | 10          | 2023-12-23 |
| 1          | linkedin.com | 15          | 2023-12-23 |

基于此，日期 2023-12-22 和 2023-12-23 之间“youtube.com”的输出应如下所示：

[
    {
        "domain": "youtube.com",
        "share_of_voice": [
            {"date": "2023-12-22", "value": 4},
            {"date": "2023-12-23", "value": 6.25}
        ]
    },
    // Entries for top 3 competitors
    {
        "domain": "google.com",
        "share_of_voice": [
            {"date": "2023-12-22", "value": 8},
            {"date": "2023-12-23", "value": 37.5}
        ]
    },
    {
        "domain": "twitter.com",
        "share_of_voice": [
            {"date": "2023-12-22", "value": 32},
            {"date": "2023-12-23", "value": 12.5}
        ]
    },
    {
        "domain": "linkedin.com",
        "share_of_voice": [
            {"date": "2023-12-22", "value": 40},
            {"date": "2023-12-23", "value": 18.75}
        ]
    },
]

如果无法以上述格式获得输出，则返回任何内容，只要它包含所有数据就没关系。

预先感谢您的帮助！

clickhouse

2 个回答

  1. Best Answer

    声明中有一些评论。

    WITH 
  toDate ('2023-12-22') as dt_start
, toDate ('2023-12-23') as dt_end
, 'youtube.com'         as site2exclude
--, 'google.com'            as site2exclude
SELECT *
FROM
(
SELECT
  *
-- propagation of the end day enumeration to all other days
, sum (num_end) over (partition by website)
    as num
FROM
(
SELECT 
  *
-- enumerating rows for the end day only, the excluded site gets the last number
, if 
  (
      date = dt_end
    , row_number () over (partition by date order by if (website = site2exclude, 0, share_of_voice) desc)
    , 0
  ) as num_end
FROM
(
SELECT 
  *
-- share_of_voice computation for each day
, round (100 * est_traffic / sum (est_traffic) over (partition by date), 2)
    as share_of_voice
FROM VALUES 
(
  'website String, est_traffic UInt32, date Date'
, ('linkedin.com', 50, toDate ('2023-12-22'))
, ('twitter.com' , 40, toDate ('2023-12-22'))
, ('facebook.com', 20, toDate ('2023-12-22'))
, ('google.com'  , 10, toDate ('2023-12-22'))
, ('youtube.com' ,  5, toDate ('2023-12-22'))
, ('google.com'  , 30, toDate ('2023-12-23'))
, ('facebook.com', 20, toDate ('2023-12-23'))
, ('linkedin.com', 15, toDate ('2023-12-23'))
, ('twitter.com' , 10, toDate ('2023-12-23'))
, ('youtube.com' ,  5, toDate ('2023-12-23'))
)
WHERE date between dt_start and dt_end
)
)
)
-- final selection of the excluded site and 3 other competitors
WHERE num between 1 and 3 or website = site2exclude
ORDER BY date, num

    结果（带有几个附加字段仅用于中间计算的可视化）是：

    |website     |est_traffic|date      |share_of_voice|num_end|num|
|------------|-----------|----------|--------------|-------|---|
|google.com  |10         |2023-12-22|8             |0      |1  |
|facebook.com|20         |2023-12-22|16            |0      |2  |
|linkedin.com|50         |2023-12-22|40            |0      |3  |
|youtube.com |5          |2023-12-22|4             |0      |5  |
|google.com  |30         |2023-12-23|37.5          |1      |1  |
|facebook.com|20         |2023-12-23|25            |2      |2  |
|linkedin.com|15         |2023-12-23|18.75         |3      |3  |
|youtube.com |5          |2023-12-23|6.25          |5      |5  |
    • 1

  2. 根据我对上述文字的理解

    1. 第一个解决方案

      SELECT sum(est_traffic)*100.0/(SELECT sum(est_traffic) FROM serp_analytics WHERE BETWEEN d1 AND d2) FROM serp_analytics WHERE date BETWEEN d1 AND d2 AND website = 'youtube.com'

    2. 第二种解决方案

      SELECT website, sum(est_traffic)*100.0/(SELECT sum(est_traffic) FROM serp_analytics WHERE date BETWEEN d1 AND d2) AS share_of_voice FROM serp_analytics WHERE date BETWEEN d1 AND d2 group BY website ORDER BY share_of_voice DESC LIMIT 3

    • 0

相关问题