主页 / dba / 问题 / 183437
Accepted
Salmononius2
Asked: 2017-08-15 06:59:36 +0800 CST

按特定列重新排序分区结果

  • 772

我有一个查询，我想在其中找到每个时区的前 50 个 event_dates。虽然我明白了，但它目前还没有以可用的方式格式化。我希望结果看起来像：

编号 | 事件日期 | 时区

1 | 2017-08-07 | 山

2 | 2017-08-08 | 山

3 | 2017-08-09 | 山

4 | 2017-08-07 | 中央

5 | 2017-08-08 | 中央

6 | 2017-08-09 | 中央

7 | 2017-08-07 | 东

8 | 2017-08-08 | 东

9 | 2017-08-09 | 东

10 | 2017-08-07 | 西

11 | 2017-08-08 | 西

12 | 2017-08-09 | 西

目前，结果在时区之间混合，如下所示：

编号 | 事件日期 | 时区

1 | 2017-08-07 | 山

2 | 2017-08-07 | 中央

3 | 2017-08-07 | 东

4 | 2017-08-07 | 西

5 | 2017-08-08 | 山

6 | 2017-08-08 | 中央

7 | 2017-08-08 | 东

8 | 2017-08-08 | 西

9 | 2017-08-09 | 山

10 | 2017-08-09 | 中央

11 | 2017-08-09 | 东

12 | 2017-08-09 | 西

我需要做什么来更新查询以这种方式输出？

作为参考，我目前拥有的是这样的：

SELECT TOP 200 ID, event_date, timezone FROM ...
ORDER BY ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone), timezone, event_date ASC
sql-server

2 个回答

  1. Best Answer

    我会先让ROW_NUMBER逻辑发生，方法是将其包含在子查询中，然后按每个时区的前 50 行进行过滤。这也使查询不受添加到 FROM 表的新时区的影响。

    SELECT *
FROM (
    SELECT ID
        ,event_date
        ,timezone
        ,ROW_NUMBER() OVER (
                PARTITION BY timezone --these are the main groups you want
                ORDER BY event_date DESC --DESC picks the LATEST dates first (ASC would pick the earliest)
            ) AS RowNumber
    FROM...
    ) Results
WHERE RowNumber <= 50 --top X rows per timezone
ORDER BY timezone, RowNumber
    • 2

  2. 您按行号排序，按时区分区。这导致每个时区的第一个实例是数字 1，每个时区的第二个实例是数字 2，等等。

    因此，结果是循环排序的。

    查看此查询以了解：

    SELECT ID
    , event_date
    , timezone
    , rn = ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone, event_date)
FROM dbo.TZ
ORDER BY rn;

    ╔════╦════════════════════════════╦═════════════════════════
║ ID ║ event_date ║ timezone ║ rn ║
╠════╬═════════════════════════╬══════════════════════════
║ 10 ║ 1905-06-24 00:00:00.000 ║ 中环 ║ 1 ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ 东部 ║ 1 ║
║ 9 ║ 1905-06-24 00:00:00.000 ║ 山 ║ 1 ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ 西方 ║ 1 ║
║ 8 ║ 1905-06-25 00:00:00.000 ║ 西方 ║ 2 ║
║ 5 ║ 1905-06-25 00:00:00.000 ║ 山 ║ 2 ║
║ 7 ║ 1905-06-25 00:00:00.000 ║ 东部 ║ 2 ║
║ 6 ║ 1905-06-25 00:00:00.000 ║ 中环 ║ 2 ║
║ 2 ║ 1905-06-26 00:00:00.000 ║ 中环 ║ 3 ║
║ 3 ║ 1905-06-26 00:00:00.000 ║ 东部 ║ 3 ║
║ 1 ║ 1905-06-26 00:00:00.000 ║ 山 ║ 3 ║
║ 4 ║ 1905-06-26 00:00:00.000 ║ 西方 ║ 3 ║
╚════╩═════════════════════════╩═════════════════════════

    您想要的排序可以在没有聚合的情况下完成：

    SELECT ID, event_date, timezone
FROM dbo.TZ
ORDER BY timezone, event_date;

    ╔════╦════════════════════════════╦═══════════
║ ID ║ 事件日期 ║ 时区 ║
╠════╬═════════════════════════╬════════════
║ 10 ║ 1905-06-24 00:00:00.000 ║ 中环 ║
║ 6 ║ 1905-06-25 00:00:00.000 ║ 中环 ║
║ 2 ║ 1905-06-26 00:00:00.000 ║ 中环 ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ 东方 ║
║ 7 ║ 1905-06-25 00:00:00.000 ║ 东方 ║
║ 3 ║ 1905-06-26 00:00:00.000 ║ 东方 ║
║ 9 ║ 1905-06-24 00:00:00.000 ║ 山 ║
║ 5 ║ 1905-06-25 00:00:00.000 ║ 山 ║
║ 1 ║ 1905-06-26 00:00:00.000 ║ 山 ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ 西方 ║
║ 8 ║ 1905-06-25 00:00:00.000 ║ 西方 ║
║ 4 ║ 1905-06-26 00:00:00.000 ║ 西方 ║
╚════╩═════════════════════════╩═════════════

    您可以使用 cte 获取所需的行编号，以将每个时区限制为前 50 个条目，如下所示：

    ;WITH cte AS 
(
    SELECT ID
        , event_date
        , timezone
        , rn = ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone, event_date)
    FROM dbo.TZ
)
SELECT *
FROM cte
WHERE cte.rn <= 50
ORDER BY cte.timezone
    , event_date;

    ╔════╦════════════════════════════╦═════════════════════════
║ ID ║ event_date ║ timezone ║ rn ║
╠════╬═════════════════════════╬══════════════════════════
║ 10 ║ 1905-06-24 00:00:00.000 ║ 中环 ║ 1 ║
║ 6 ║ 1905-06-25 00:00:00.000 ║ 中环 ║ 2 ║
║ 2 ║ 1905-06-26 00:00:00.000 ║ 中环 ║ 3 ║
║ 11 ║ 1905-06-24 00:00:00.000 ║ 东部 ║ 1 ║
║ 7 ║ 1905-06-25 00:00:00.000 ║ 东部 ║ 2 ║
║ 3 ║ 1905-06-26 00:00:00.000 ║ 东部 ║ 3 ║
║ 9 ║ 1905-06-24 00:00:00.000 ║ 山 ║ 1 ║
║ 5 ║ 1905-06-25 00:00:00.000 ║ 山 ║ 2 ║
║ 1 ║ 1905-06-26 00:00:00.000 ║ 山 ║ 3 ║
║ 12 ║ 1905-06-24 00:00:00.000 ║ 西方 ║ 1 ║
║ 8 ║ 1905-06-25 00:00:00.000 ║ 西方 ║ 2 ║
║ 4 ║ 1905-06-26 00:00:00.000 ║ 西方 ║ 3 ║
╚════╩═════════════════════════╩═════════════════════════

    根据源表中的行数，在 timezone 和 event_date 上建立索引并包括 ID 列（假设它不是表的主键）可能是有利的。

    • 2

相关问题