时间戳

首页 > 解决方案 > 窗口函数从每个组中获取第一行和最后一行

sql - 窗口函数从每个组中获取第一行和最后一行

问题描述

我在 presto 上使用窗口函数来获取每组的第一行和最后一行。我确实ROW_NUMBER()在我的名称列上应用了百分比列排序的分区，我得到了以下结果

当前查询：

SELECT Name, Price, Percent, Volume, time, date,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Percent DESC) AS rn
FROM TABLE_NAME  ORDER BY Name asc

电流输出：

Name    Price   Percent  Volume              time          date    rn
AABB    0.015   42.55   25980719    2020-12-29 10:23:11 2020-12-29  1
AABB    0.014   33.33   22640655    2020-12-29 10:20:42 2020-12-29  2
AABB    0.014   33.33   22640655    2020-12-29 10:21:11 2020-12-29  3
AABB    0.0137  30.0    21466099    2020-12-29 10:19:20 2020-12-29  4
AABB    0.0135  28.57   20461208    2020-12-29 10:17:19 2020-12-29  5
AABB    0.013   23.81   20201208    2020-12-29 10:16:41 2020-12-29  6
AABB    0.013   23.81   19129182    2020-12-29 10:15:20 2020-12-29  7
AABB    0.0125  19.05   14513969    2020-12-29 10:07:15 2020-12-29  8
AABB    0.0125  19.05   15580088    2020-12-29 10:09:14 2020-12-29  9
AABB    0.012   14.29   14313969    2020-12-29 10:06:44 2020-12-29  10
AABB    0.012   14.29   12924448    2020-12-29 10:15:14 2020-12-29  11
ABQQ    0.025   74.83   6809380     2020-12-29 09:50:04 2020-12-29  1
ABQQ    0.024   67.83   4196759     2020-12-29 09:48:10 2020-12-29  2
ABQQ    0.0225  57.34   935554      2020-12-29 09:06:13 2020-12-29  3
ABQQ    0.0143  -5.61   1600927     2020-12-29 09:43:51 2020-12-29  4
ABQQ    0.0143  -5.61   1600927     2020-12-29 09:41:51 2020-12-29  5
ABQQ    0.0143  -5.61   1600927     2020-12-29 09:36:52 2020-12-29  6

预期输出 1：（按百分比排序并仅选择最高 + 最低百分比值行）

Name    Price   Percent  Volume              time          date    rn
AABB    0.015   42.55   25980719    2020-12-29 10:23:11 2020-12-29  1
AABB    0.012   14.29   12924448    2020-12-29 10:15:14 2020-12-29  11
ABQQ    0.025   74.83   6809380     2020-12-29 09:50:04 2020-12-29  1
ABQQ    0.0143  -5.61   1600927     2020-12-29 09:36:52 2020-12-29  6

预期输出2：（按时间排序并仅选择最高+最低时间值行）

Name    Price   Percent  Volume              time          date    rn
AABB    0.015   42.55   25980719    2020-12-29 10:23:11 2020-12-29  1
AABB    0.012   14.29   14313969    2020-12-29 10:06:44 2020-12-29  10
ABQQ    0.025   74.83   6809380     2020-12-29 09:50:04 2020-12-29  1
ABQQ    0.0225  57.34   935554      2020-12-29 09:06:13 2020-12-29  3

标签： sqlwindow-functionsprestoamazon-athenatrino

解决方案

你需要一个子查询：

SELECT Name, Price, Percent, Volume, time, date,
FROM (SELECT t.*,
               ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Percent) AS seqnum_asc,
               ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Percent DESC) AS seqnum_desc
      FROM TABLE_NAME t
     ) t
WHERE 1 IN (seqnum_asc, seqnum_desc)
ORDER BY Name asc;

如果您想time作为单独的查询，只需调整ORDER BY窗口子句中的 。如果你想要一个查询，那么添加两个新的“seqnum”，基于time.

推荐阅读