主页 / user-3790006

emi's questions

Martin Hope
emi
Asked: 2023-12-23 09:23:16 +0800 CST
  • 6

我需要有关编写查询来分析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