使用Teradata中的存储过程来构建摘要历史表

Question

我正在使用连接到企业DW的Terdata SQL助手。我已经写了以下查询，以显示特定时间点的未来项目清单。该表引用了负载和存储新记录，作为通过负载日期对其状态进行更改（并且不会删除历史记录）。我查询的输出是指定日期的1行。我可以创建一个存储过程或某种递归查询以构建这些摘要行的历史记录（每天有1行）？我过去没有使用过这样的功能。与以前回答的有关我如何在研究其他可能的解决方案方面的问题或建议的链接是完全可以的；只是试图在我的知识上弥合这一差距。

SELECT
'2017-10-02' as Dt
,COUNT(DISTINCT A.RECORD_NBR) as Pending_Records 
,SUM(A.PAY_AMT) AS Total_Pending_Payments
FROM DB.RECORD_HISTORY A 
INNER JOIN
(SELECT MAX(LOAD_DT) AS LOAD_DT
,RECORD_NBR
FROM DB.RECORD_HISTORY
WHERE  LOAD_DT <= '2017-10-02' 
GROUP BY RECORD_NBR
) B 
ON A.RECORD_NBR = B.RECORD_NBR
AND A.LOAD_DT = B.LOAD_DT
WHERE 
A.RECORD_ORDER =1 AND Final_DT Is  Null   
GROUP BY Dt
ORDER BY 1 desc

Answer 1

这是我对查询的解释：

对于最新的load_dt（直到2017-10-02），record_order＃1， 返回

1）不同待处理记录的数量
2）待付款的总金额

这是正确的吗？如果您正在寻找此信息，但是每个" load_dt"一行，您只需要删除内心即可：

SELECT 
    load_Dt,
    COUNT(DISTINCT record_nbr) AS Pending_Records,
    SUM(pay_amt) AS Total_Pending_Payments
FROM DB.record_history
WHERE record_order = 1
AND final_Dt IS NULL
GROUP BY load_Dt
ORDER BY 1 DESC

如果您想获得每个record_order的摘要信息，只需添加record_order作为分组列：

SELECT 
    load_Dt,
    record_order,
    COUNT(DISTINCT record_nbr) AS Pending_Records,
    SUM(pay_amt) AS Total_Pending_Payments
FROM DB.record_history
WHERE final_Dt IS NULL
GROUP BY load_Dt, record_order
ORDER BY 1,2 DESC

如果您想每天获得一排（如果有没有相应的" load_dt"天的日历天数），则可以从sys_calendar.calendar.calendar视图中进行选择，并在" load_dt"字段上左上加入上面的查询：

SELECT cal.calendar_date, src.Pending_Records, src.Total_Pending_Payments
FROM sys_calendar.calendar cal
LEFT JOIN (
    SELECT 
        load_Dt,
        COUNT(DISTINCT record_nbr) AS Pending_Records,
        SUM(pay_amt) AS Total_Pending_Payments
    FROM DB.record_history
    WHERE record_order = 1
    AND final_Dt IS NULL
    GROUP BY load_Dt
) src ON cal.calendar_date = src.load_Dt
WHERE cal.calendar_date BETWEEN <start_date> AND <end_date>
ORDER BY 1 DESC

我无法访问TD系统，因此您可能会获得语法错误。让我知道那是有效的还是您正在寻找其他东西。