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屋！