Teradata 中的测序 [英] Sequencing in Teradata
问题描述
我想创建一个 SELECT 查询,该查询会生成一个有 30 个字段长和 1 个记录厚的视图.如果我们计算标题,则为 2 条记录.
I would like to create a SELECT query that results in a view that is 30 fields long and 1 record thick. 2 records if we're counting the title.
有问题的较大查询的具体部分是:
The specific part of the larger query in question is:
WHERE CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 1
这会产生所需的结果 - 记录 EVENT_TIMESTAMP 和 CONTRACT_EFFECTIVE_DATE 之间的天数为 1.
This produces the results desired - records where the days between EVENT_TIMESTAMP and CONTRACT_EFFECTIVE_DATE is 1.
但我想要这个 30 天.类似的东西:
But I'd like this for 30 days. Something like:
WHERE CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 1:30
这可能吗?我可以只剪切 n 粘贴查询 30 次并将数字 1 更改为相应的值.但肯定有更好的方法吗?
Is that possible? I could just cut n paste the query 30 times and change the number 1 to the corresponding value. But surely there is a better way?
结果类似于:
1 day 2 day 3 day 4 day
10 11 8 14
推荐答案
这是另一个 PIVOT 查询,再次进行大量剪切和粘贴.
That's another PIVOT query, lots of cut&paste again.
假设您要计算匹配每一天范围的行数:
Assuming you want to count the number of rows matching each day range:
SELECT
COUNT(CASE WHEN CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 1 THEN 1 END) AS "1 day",
COUNT(CASE WHEN CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 2 THEN 1 END) AS "2 day",
COUNT(CASE WHEN CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 3 THEN 1 END) AS "3 day",
COUNT(CASE WHEN CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE = 4 THEN 1 END) AS "4 day",
...
FROM tab
WHERE CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE <= 30
您也可以将计算放在派生表中,但这只是看起来更简单:
You could also put the calculation in a Derived Table, but this just looks simpler:
SELECT
COUNT(CASE WHEN diff = 1 THEN 1 END) AS "1 day",
COUNT(CASE WHEN diff = 2 THEN 1 END) AS "2 day",
COUNT(CASE WHEN diff = 3 THEN 1 END) AS "3 day",
COUNT(CASE WHEN diff = 4 THEN 1 END) AS "4 day"
FROM
(
SELECT CAST(EVENT_TIMESTAMP AS DATE) - CONTRACT_EFFECTIVE_DATE AS diff
FROM tab
WHERE diff <= 30
) AS dt
这篇关于Teradata 中的测序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!