如何逐行检索去年的数据（主集按年、月分组并按数量汇总）？

2024-05-20 • 问答

有没有办法在卷聚合期间轻松检索去年的数据，按年、月分组。

以下代码示例（来自 BQ）。它显示子查询中的错误 WHERE clause expression references t1.date 既不分组也不聚合

SELECT
EXTRact(YEAR FROM t1.date) AS year,EXTRact(MONTH FROM t1.date) AS month,t1.ProductId AS product,SUM(t1.Quantity) AS UnitsSold_TY,(SELECT
    SUM(Quantity)
FROM `new-project-jun21.sales.sales_info`
WHERE
    EXTRact(YEAR FROM date) =  EXTRact(YEAR FROM t1.date) - 1 AND
    EXTRact(MONTH FROM date) = EXTRact(MONTH FROM t1.date) AND
    ProductId = t1.ProductId
GROUP BY
    EXTRact(YEAR FROM date),EXTRact(MONTH FROM date),EXTRact(MONTH FROM t1.date),ProductId) AS UnitsSold_LY
FROM `new-project-jun21.sales.sales_info` AS t1 
GROUP BY
year,month,product
ORDER BY product,year,month

在功能上，这应该是等价的，但我可能会尽量避免相关子查询：

WITH xrows AS (
      SELECT EXTRACT(YEAR FROM t1.date)  AS year,EXTRACT(MONTH FROM t1.date) AS month,t1.ProductId AS product,SUM(t1.Quantity) AS UnitsSold_TY
        FROM `new-project-jun21.sales.sales_info` AS t1 
       GROUP BY year,month,product
     )
SELECT t.*,( SELECT SUM(Quantity)
           FROM `new-project-jun21.sales.sales_info`
          WHERE EXTRACT(YEAR FROM date)  = t.year - 1
            AND EXTRACT(MONTH FROM date) = t.month
            AND ProductId = t.ProductId
       ) AS UnitsSold_LY
  FROM xrows t
 ORDER BY product,year,month
;

调整删除相关子查询中不必要的GROUP BY。

如果您每个月都有数据，那么您可以使用<shape xmlns:android="http://schemas.android.com/apk/res/android" android:shape="rectangle"> <solid android:color="@color/colaba_grey_fill" /> <stroke android:width="0.5dp" android:color="#4484F4" /> <corners android:radius="100dip" /> </shape>。我建议使用 lag() 而不是分隔年份和日期组件。所以：

date_trunc()

如果有些产品缺月，那么还是可以用窗口函数，只是逻辑有点复杂。

编辑：

如果您没有每个月的数据，那么您可以使用SELECT productId,DATE_TRUNC(date,INTERVAL 1 MONTH) as yyyymm SUM(Quantity),LAG(SUM(Quantity),12) OVER (PARTITION BY ProductId ORDER BY MIN(date)) as yoy FROM `new-project-jun21.sales.sales_info` GROUP BY product_id,INTERVAL 1 MONTH); ORDER BY product,yyyymm; 规范，但您需要一个月份计数器：

RANGE

如何逐行检索去年的数据（主集按年、月分组并按数量汇总）？

bill58702738 回答：如何逐行检索去年的数据（主集按年、月分组并按数量汇总）？

大家都在问