我有一个表格，需要对每种材料进行汇总。我已经用这个做到了：
SELECT *, SUM(`ESTOQUE_FINAL`) OVER (PARTITION BY MATERIAL ORDER BY CALDAY) as ESTOQUE
FROM bq_trusted.IINV_01
ORDER BY MATERIAL, CALDAY
结果在此屏幕截图中
我现在需要的是获得红色圆圈值的结果。对于每种材料，我需要在每个月底之前得出汇总结果。
我可以使用以下查询获得这些结果，但必须将上一个查询保存在新表中才能使用。
WITH ESTOQUE_ATUAL AS (
SELECT IQ.*, ROW_NUMBER() OVER (PARTITION BY MATERIAL, MONTH_YEAR ORDER BY CALDAY DESC) AS RN
FROM bq_trusted.INVENTORY AS IQ
)
SELECT * FROM ESTOQUE_ATUAL WHERE RN = 1
ORDER BY MONTH_YEAR
如何仅使用一个查询就可以达到此结果？
提前致谢！
这是你想要的吗？
SELECT i.*
FROM (SELECT i.*,
SUM(ESTOQUE_FINAL) OVER (PARTITION BY MATERIAL ORDER BY CALDAY) as ESTOQUE,
ROW_NUMBER() OVER (PARTITION BY MATERIAL, MONTH_YEAR ORDER BY CALDAY DESC) AS seqnum
FROM bq_trusted.IINV_01 i
) i
WHERE seqnum = 1
ORDER BY MATERIAL, CALDAY
您可以同时计算两个窗口函数。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句