Início / coding / Perguntas / 77706362
Accepted
emi
Asked: 2023-12-23 09:23:16 +0800 CST

Consulta SQL para calcular a participação diária de voz com análise dos principais concorrentes

  • 772

Preciso de ajuda para escrever uma consulta para analisar dados de tráfego do site a partir de uma serp_analyticstabela. A estrutura da tabela é a seguinte:

coluna tipo
palavra-chave_id interno
local na rede Internet varchar
est_traffic interno
data data hora

Meu objetivo é calcular o "Share of Voice" diário do meu site ('youtube.com') e identificar os 3 principais sites concorrentes com base em seu share of voice. A análise deve abranger um intervalo de datas específico, fornecido como :startparâmetros :end.

Requisitos Específicos:

  1. Calcular a participação diária de voz: preciso calcular a participação diária de voz para 'youtube.com'. A participação de voz é calculada como a soma de est_traffic'youtube.com' em um determinado dia, dividida pelo total est_trafficde todos os sites naquele dia, multiplicado por 100.

  2. Identifique os três principais concorrentes: com base na participação de voz na :enddata, determine os três principais sites (excluindo 'youtube.com'). Esses principais concorrentes devem ser incluídos na produção de cada dia no intervalo de datas especificado.

  3. Estrutura de saída: A saída desejada é um formato estruturado onde cada entrada corresponde a um site, incluindo 'youtube.com' e os 3 principais concorrentes. Cada entrada deve listar a participação diária de voz para o intervalo de datas. Se um site não tiver dados em um determinado dia, sua participação de voz deverá ser mostrada como 0.

  4. Cobertura completa de datas: a saída deve incluir todas as datas no intervalo entre :starte :end. Se não houver dados de um site em uma data específica, o share of voice para essa data deverá ser 0.

Aqui está um exemplo de conjunto de dados:

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

Com base nisso, a saída para 'youtube.com' entre as datas 2023/12/22 e 2023/12/23 deve ser semelhante a esta:

[
    {
        "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}
        ]
    },
]

Se não for possível obter a saída no formato acima, retorne qualquer coisa que realmente não importe, desde que tenha todos os dados.

Agradeço antecipadamente por sua ajuda!

clickhouse

2 respostas

  1. Best Answer

    Alguns comentários estão no comunicado.

    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

    O resultado (com alguns campos adicionais apenas para visualização de cálculos intermediários) é:

    |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. com base na minha compreensão do texto acima

    1. primeira solução

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

    2. segunda solução

      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

relate perguntas