sql - (Teradata) 获取前 12 个月、3 个月和 1 个月的 SUM 值
问题描述
我有一个正在使用的查询,我需要获取前 12 个月、3 个月和上个月的值,但不知道该怎么做。
我当前的查询如下:
select top 100
A.invoice_nbr
, A.invoice_date
, A.ship_from_dc_nbr
, A.ship_to_store_nbr
, A.trailer_id
, B.item_nbr
, C.old_nbr
, C.whpk_qty
, C.vnpk_qty
, C.item_type_code
, D.po_type
, SUM(B.each_ship_qty) as each_ship_qty
, SUM(B.variable_wt_qty) as variable_wt_qty
, SUM(B.ext_cost_amt) as ext_cost_amt
from DB.DC_INVOICE A
inner join DB.DC_INVOICE_LINE B ON A.invoice_nbr = B.invoice_nbr and A.invoice_date = B.invoice_date and A.ship_to_store_nbr = B.ship_to_store_nbr
inner join DB.item C on B.item_nbr = C.item_nbr
left join DB.purchase_order D on B.po_nbr = D.po_nbr
--where A.invoice_date = '2019-09-05' and A.ship_from_dc_nbr = 6011
group by
A.invoice_nbr
, A.invoice_date
, A.ship_from_dc_nbr
, A.ship_to_store_nbr
, A.trailer_id
, B.item_nbr
, C.old_nbr
, C.whpk_qty
, C.vnpk_qty
, C.item_type_code
, D.po_type
我尝试为“12 个月”添加以下内容作为另一列:
(SELECT SUM(vnpk_qty)
FROM DB.item C
inner join DB.DC_INVOICE_LINE B ON B.item_nbr = C.item_nbr
inner join DB.DC_INVOICE A ON A.invoice_nbr = B.invoice_nbr and A.ship_to_store_nbr = B.ship_to_store_nbr and BETWEEN DATEADD(dd, A.invoice_date,-365) AND DATEADD(dd, A.invoice_date,-1)
)
然而,这给了我错误:
[Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between the 'and' keyword and the 'BETWEEN' keyword.
任何有关如何获取我正在寻找的数据的建议将不胜感激。
解决方案
您可以使用条件聚合逻辑和ADD_MONTHS
函数。这是一般的想法:
SELECT
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -12) THEN vnpk_qty END
) AS sum_12month,
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -3) THEN vnpk_qty END
) AS sum_3month,
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -1) THEN vnpk_qty END
) AS sum_1month
FROM DB.DC_INVOICE A
因此,根据您的查询,它看起来像:
select top 100
A.invoice_nbr
, A.invoice_date
, A.ship_from_dc_nbr
, A.ship_to_store_nbr
, A.trailer_id
, B.item_nbr
, C.old_nbr
, C.whpk_qty
, C.vnpk_qty
, C.item_type_code
, D.po_type
, SUM(B.each_ship_qty) as each_ship_qty
, SUM(B.variable_wt_qty) as variable_wt_qty
, SUM(B.ext_cost_amt) as ext_cost_amt
-- New fields
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -12) THEN C.vnpk_qty END
) AS sum_12month,
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -3) THEN C.vnpk_qty END
) AS sum_3month,
SUM(
CASE WHEN A.invoice_date > ADD_MONTHS(CURRENT_DATE, -1) THEN C.vnpk_qty END
) AS sum_1month
-- End new fields
from DB.DC_INVOICE A
inner join DB.DC_INVOICE_LINE B ON A.invoice_nbr = B.invoice_nbr and A.invoice_date = B.invoice_date and A.ship_to_store_nbr = B.ship_to_store_nbr
inner join DB.item C on B.item_nbr = C.item_nbr
left join DB.purchase_order D on B.po_nbr = D.po_nbr
--where A.invoice_date = '2019-09-05' and A.ship_from_dc_nbr = 6011
group by
A.invoice_nbr
, A.invoice_date
, A.ship_from_dc_nbr
, A.ship_to_store_nbr
, A.trailer_id
, B.item_nbr
, C.old_nbr
, C.whpk_qty
, C.vnpk_qty
, C.item_type_code
, D.po_type
您可以根据需要将类似的逻辑应用于您的其他SUM()
s。这仍然是正常的,SUM
除非您根据invoice_date
.
推荐阅读
- python - 如何在 for 循环中命名数据帧?
- highcharts - 在高图表上悬停时围绕轴的环可变半径饼图
- php - ErrorException 试图获取非对象 laravel 的属性
- c++ - 如何制作构造函数的深层副本
- node.js - 使用 Reactjs 应用程序作为域后端中 express nodejs 的前端
- html - SVG 徽标无法在移动设备上正确显示
- python - 为 RabbitMQ 保持打开连接还是仅在必要时打开连接更好?
- react-native - 捕获新视频时如何不捕获设备上运行的音频播放
- apache-spark - Pyspark 在返回 0 的双精度数上转换整数
- visual-studio-code - vscode如何让“打字屏”变大