如何计算MoM和YoY？

Question

我需要获取所有度量列的MoM和YoY值。MoM列应该有上个月的值，YoY也是如此。我可以用一个维度来做，但由于有很多维度，所以得到的信息是错误的。

以下是当我为所有维度设置筛选器时正在运行的查询：

SELECT section,region,country,type,device,month,page_views,
LAG(page_views, 1) OVER (ORDER BY month) as MoM,
LAG(page_views, 12) OVER (ORDER BY month) as YoY
FROM table_name
Where region = 'RUCIS'
 and Country = 'Russia'
 and section ='METRICS' 
 and type_visits = 'All Visits'
 and device = 'Non Mobile Devices'

它工作得很好，但如果我删除where条件，因为有许多地区和国家，在这种情况下，它不能工作。

下面是一些示例数据

​

如何对所有维度执行此操作？

Answer 1

如果代码使用where子句工作，但您希望它在没有子句的情况下工作，则可以使用partition by

SELECT section, region, country, type, device, month, page_views,
       LAG(page_views, 1) OVER (PARTITION BY section, region, country, type_visits, device
                                ORDER BY month) as MoM,
       LAG(page_views, 12) OVER (PARTITION BY section, region, country, type_visits, device
                                 ORDER BY month) as YoY
FROM table_name
WHERE region = 'RUCIS' and
      Country = 'Russia' and
      section ='METRICS' and
      type_visits = 'All Visits' and
      device = 'Non Mobile Devices';

现在，您应该能够删除WHERE子句或根据您的需要对其进行修改。