用于选择季度并对数据执行聚合的动态SQL查询

Question

sl..   Country  Channel  Type  Clicks    Spend     Impressions    Date       
1.     india    Social   a     14        $25       1,331          2/11/2021     
2.     india    Search   b     1,748     $1,801    1,166,140      2/11/2021     
3.     india    Display  c     28,615    $3,901    8,279,595      2/11/2021     
4.     india    Display  a     1,500     $1,000    1,233          7/10/2020     
5.     india    Display  a     11,500    $500      5,133          10/1/2020     
6.     india    Display  a     599       $200      6570           1/1/2020      

因此，我需要做的是根据历史数据创建每个季度的点击量/印象。历史数据是相同的，只是日期不同，现在为本季度创建(点击/印象)值将是

select sum(clicks)/sum(impressions)
from table_name
groupby (country, channel, type)
where ________

我需要where子句中的帮助，我只需要选择某些特定季度的数据，同时为任何季度生成点击/印象，选择的逻辑是：

Sum(clicks(q3 2020,q4 2020, q1 2020))/Sum(impressions(q3 2020,q4 2020, q1 2020))

在这里我们需要从date列中查找季度和年份。动态地说，我的意思是，如果我们进入下一个季度，那么我需要比较过去两个季度和去年同一季度的平均值。

我编写了从日期开始查找季度和年份的代码，但是如何继续呢？

CASE

    WHEN EXTRACT(MONTH FROM day) BETWEEN 7 AND 9 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 10 AND 12 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 1 AND 3 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM day) BETWEEN 4 AND 6 THEN 'Q4'
END AS quarter,
EXTRACT(Year FROM day) AS Year 

期望输出

slno.  quarter  clicks/impressions
1.     Q1 2021  0.53
2.     Q4 2020  1.35
.......

Answer 1

当你试图过滤数据时，不要对数据做复杂的数学运算并过滤结果。这需要处理整个表，然后丢弃不需要的行。

相反，在过滤器参数中进行数学运算，这将允许数据库通过检查索引并仅加载它需要的行来完成过滤。

举个例子。

SELECT
   SUM(clicks) / SUM(impressions)
FROM
    yourTable
WHERE
      (Date >= '2020-07-01' AND Date < '2021-01-01')
   OR (Date >= '2020-01-01' AND Date < '2020-04-01')

这确保您只处理2020年最后两个季度的数据，以及2020年第一季度的数据。

然后问题就变成了，如何根据今天的日期计算出这些日期。

当前季度的开始可以由此提供...How do I get the first date of a quarter in MySQL?

如果您将计算结果存储在名为@CurrentQuarterStart的变量中，则WHERE子句将变为...

WHERE
      (Date >= @CurrentQuarterStart - INTERVAL 2 QUARTERS AND Date < @CurrentQuarterStart)
   OR (Date >= @CurrentQuarterStart - INTERVAL 4 QUARTERS AND Date < @CurrentQuarterStart - INTERVAL 3 QUARTERS)