Teradata 结果中的第 n 个结果

Question

我正在尝试创建一个 Teradata SQL 查询，该查询将返回每个用户的第 n 个按时间顺序排列的访问日期。例如，

user  |  visit_date
---------------------
  a      1/1      
  b      1/10
  c      1/20
  a      1/3
  a      1/4
  b      1/5
  c      1/15
  b      1/9


> magic_query_for_Second_visit;
user  |  second
------------------
  a       1/3
  b       1/9
  c       1/20

我尝试了类似下面的内容，但 Teradata 尖叫道“WHERE 子句中不允许使用有序分析函数。”我拉头发有一段时间了，但没有取得太大进展。有人见过这个吗？

select user,
  row_number() over (partition by user order by visit_date desc) as rnum

from visitstable
  where rnum = 2

如果我排除 where 那么我的结果看起来很有希望......我只是无法提取我需要的东西！

 user |  visit_date | rnum
---------------------------
  a        1/1          1
  a        1/3          2
  a        1/4          3
  b        1/5          1
  b        1/9          2
  b        1/10         3 
  c        1/15         1
  c        1/20         2

预先感谢您的帮助！

Answer 1

SELECT 
    user

FROM 
    visitstable

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY 
        user
    ORDER BY 
        visit_date DESC) = 2

我从您的评论中看到，您不一定想要依赖 Teradata 特定的 QUALIFY。以下是基于另一小伙子答案的一种便携式解决方案（因为许多 RDBMS 现在支持 CTE）：

WITH tmp (user, rnum) AS (
    SELECT 
        user, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                user 
            ORDER BY 
                visit_date DESC) AS rnum 
    FROM 
        visitstable)
SELECT
    tmp.*
FROM 
    tmp
WHERE 
    tmp.rnum = 2

Answer 2

哦，天哪，我应该查看文档。仅供参考，看起来 qualify 可以让您指定有序的分析约束。然而，qualify 不是 ANSI，所以我仍在为便携式解决方案提供积分...

我可以做：

select user,
  row_number() over (partition by user order by visit_date desc) as rnum

from visitstable
qualify rnum=2

...并获得...

user  |   visit_date  |  rnum
-----------------------------
  a        1/3            2
  b        1/9            2
  c        1/20           2

添加书签！ :)

Answer 3

您使用什么版本的 SQL？它看起来像 MS SQL Server 的更高版本，因此请尝试使用像这样的“公用表表达式”...


其中 cte 为 (
选择用户，
row_number() over (按用户顺序按访问日期 desc 分区) as rnum
来自可访问表
)

从 cte 中选择 *
其中 rnum = 2
语法

可能需要一些调整，但这是一般原则。