时间戳

首页 > 解决方案 > sql：具有属性的最后一行之后的第一行

sql - sql：具有属性的最后一行之后的第一行

问题描述

我想编写一个查询，该查询在具有给定属性的最后一行之后立即返回第一行（按 id 排序）。ID 可能不是连续的。

理想情况下，它看起来像这样：

...
JOIN (select max(id) id from my_table where CONDITION) m
JOIN (select min(id) from my_table where id > m.id) n

但是，我不能m在第二个子选择中使用标识符。

可以在嵌套查询中使用嵌套查询，但有没有更简单的方法？

谢谢你。

标签： sqlsnowflake-cloud-data-platform

解决方案

我不确定这是如何融入您的其余查询的，所以我使用了 CTE

WITH max_next AS (
    SELECT r.id as max_id
        ,r.next_id
    FROM (
        SELECT m.id
            ,m.next_id
            ,ROW_NUMBER() OVER (ORDER BY m.id DESC) AS rn
        FROM (
            SELECT n.* -- to provide data to satisfy CONDITIONS
                ,LEAD(n.id) OVER(ORDER BY n.id) as next_id
            FROM my_table AS n
        ) AS m
        WHERE CONDITIONS
    ) AS r
    WHERE r.rn = 1
)

我还将 n.* 缩小到 CONDITIONS 所需的列到 a，而不是隐含的，因为 * 会减慢编译时间（或历史上有），因为需要读取所有元数据以了解 ANY 中的列，虽然编译也可以修剪未使用的列，但如果您只询问您想要的内容，它会更快（在最好的情况下只是节省编译时间，更糟糕的情况是，当您只需要读取 x 列时它会读取所有数据）

并且借用 Gordon 解决方案，这ROW_NUMBER部分可能会更简单

WITH max_next AS (
    SELECT m.id
        ,m.next_id
        --, plus what ever other things you want from m
    FROM (
        SELECT n.* -- to satisfy CONDITIONS needs
            ,LEAD(n.id) OVER(ORDER BY n.id) as next_id
        FROM my_table AS n
    ) AS m
    WHERE CONDITIONS
    ORDER BY m.id DESC LIMIT 1
)

以@PIG 为例，

WITH my_table AS (
    SELECT column1 AS id
        ,column2 AS con1
        ,column3 AS other
    FROM VALUES (1,'a',123),(2,'b',234),(3,'a',345),(5,'b',456),(7,'a',567),(10,'c',678)
)
SELECT m.id
    ,m.next_id
    ,m.other
FROM (
    SELECT n.* -- to satisfy CONDITIONS needs
        ,LEAD(n.id) OVER(ORDER BY n.id) as next_id
    FROM my_table AS n
) AS m
WHERE m.con1 = 'b'
ORDER BY m.id DESC LIMIT 1;

给出5, 7, 456最后'b'一行和新行，以及 my_table 上用于娱乐目的的额外值（并在 Snowflake 上运行，这意味着我也修复了先前的 SQL。）

推荐阅读