Teradata 获取前两天的行计数并进行比较

Question

我正在尝试设置数据检查，我们从表中获取今天和前一个日期的行数。 由于它不在周末或节假日加载，所以我不能说 DATE-1。

我带着以下内容来获取上一个日期：

SELECT
LOAD_DATE
,COUNT(LOAD_DATE) RW_COUNT
,ROW_NUMBER() OVER (ORDER BY LOAD_DATE ) AS LOAD_ROWNUM
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1

这将生成日期、计数并分配行号。

LOAD_DATE   RW_COUNT    LOAD_ROWNUM
2019-10-16  8259        1
2019-10-15  8253        2
2019-10-11  8256        3
2019-10-10  8243        4

我取两个最新的日期并进行比较。 最当前将是"当前"，第二个最当前将是"先前"。 然后我想有这样的东西作为结果集：

CURRENT_COUNT   PRIOR_COUNT DIFF_PERCENT
8259            8253        .9927

我的问题是，如何引用前两行并将它们相互比较？除非我想太多了，否则我需要两个额外的 SELECT 语句：一个带有 WHERE 子句引用第 1 行，另一个带有 WHERE 引用第 2 行的 WHERE 语句。

我该怎么做？ 我有两个 CTE 吗？
最终，我需要第三个 SELECT 将两行分开并检查 10% 的容差。 救命，我处于分析瘫痪状态。

Answer 1

您可以使用 QUALIFY 筛选 OLAP 函数的结果：

SELECT
LOAD_DATE
,COUNT(LOAD_DATE) AS CURRENT_COUNT
-- previous day's count
,LEAD(RW_COUNT)
OVER (ORDER BY LOAD_DATE DESC) AS PRIOR_COUNT
-- if your TD version doesn't support LAG/LEAD (i.e. < 16.10)
--,MIN(RW_COUNT) 
-- OVER (ORDER BY LOAD_DATE DESC
--       ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS PRIOR_COUNT
,CAST(CURRENT_COUNT AS DECIMAL(18,4)) / PRIOR_COUNT AS DIFF_PERCENT
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1
-- return the latest row only
QUALIFY ROW_NUMBER() OVER (ORDER BY LOAD_DATE DESC) = 1

检查 10% 公差：

DIFF_PERCENT BETWEEN 0.9 and 1.1

要么符合资格，要么在案例中

Answer 2

我不知道你想要的结果集是什么。 但是，您可以将LAG()与聚合一起使用来获取以前的值。

SELECT LOAD_DATE, COUNT(*) as RW_COUNT,
LAG(COUNT(*)) OVER (ORDER BY LOAD_DATE) as PREV_RW_COUNT
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1;

您可能只想要两个计数的差异。

Answer 3

如果您的TD版本(16.0+？(不支持LEAD/LAG，请尝试一下：

SELECT 
load_date, 
RW_COUNT, 
MAX(RW_COUNT) OVER(
ORDER BY load_date DESC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING -- Get previous row's value
) AS RW_COUNT_prev
FROM (
SELECT load_date, COUNT(LOAD_DATE) RW_COUNT,
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1
) src