按日期分组并显示 X（列）的值，该值取决于该日期的最短时间和该日期在 1 行中的最大时间

Question

我有像这样的桌子

id  u_id     date       time      x

---|-----|------------|--------|-----

1  | 1   | 20200806   | 0900   | 60 

2  | 2   | 20200806   | 0905   | 60 

3  | 3   | 20200806   | 0910   | 61 

4  | 1   | 20200806   | 1015   | 62 

5  | 1   | 20200806   | 1830   | 61 

6  | 3   | 20200807   | 0915   | 61 

7  | 1   | 20200807   | 0920   | 62 

8  | 2   | 20200807   | 1820   | 63 

9  | 1   | 20200807   | 1835   | 59 

我想按日期分组，哪个用户的 id 为 u_id =1 并获取 x 的第一次时间和值，并获取同一行中 x 的最后一次时间和值

它应该像

   date       firstTime  firstX   lastTime   lastX

   -----------|----------|--------|----------|----------

   20200806   |   0900   |   60   |   1830   | 61

   20200807   |   0920   |   62   |   1835   | 59

我尝试过什么

select

            p.created_date as date,

            min(p.created_time) as firstTime,

            max(p.created_time) as lastTime,

from

     passes as p

where

      p.id=1

group by p.created_date;

但我无法获取 xs 的值。

行：

Answer 1

一种方法使用条件聚合：select p.created_date as date,       min(p.created_time) as firstTime,       max(p.created_time) as lastTime,       max(case when seqnum = 1 then x end) as first_x,       max(case when seqnum_desc = 1 then x end) as last_x,from (select p.*,             row_number() over (partition by id order by created_time) as seqnum,             row_number() over (partition by id order by created_time desc) as seqnum_desc      from passes p     ) pwhere p.id=1group by p.created_date;您还可以将条件聚合表述为：       max(x) filter where (seqnum = 1) as first_x,       max(x) filter where (seqnum_desc = 1) as last_x,另一种方法使用数组：select p.created_date as date,       min(p.created_time) as firstTime,       max(p.created_time) as lastTime,)       (array_agg(p.x order by p.created_date asc))[1] as first_x,       (array_agg(p.x order by p.created_date desc))[1] as last_xfrom passes pwhere p.id = 1group by p.created_date;

Answer 2

我将使用first_value()和last_value()窗口函数来实现此目的：select distinct        "date",        first_value(time) over w as first_time,       first_value(x) over w as first_x,       last_value(time) over w as last_time,       last_value(x) over w as last_x  from passes where u_id = 1window w as (partition by u_id                 order by date          rows between unbounded preceding                   and unbounded following);在这里工作小提琴

Answer 3

我能够通过创建子查询来重现您的结果。子查询按日期对值进行分组并返回第一次和最后一次。通过确定第一次和最后一次的日期，我做了两次连接，一次是为了获取第一个 X，另一个是为了获取最后一个 X。通过使用 mySQL 作为引擎，我在http://sqlfiddle.com/上执行了以下步骤：构建架构：CREATE TABLE passes    (`id` int, `u_id` int, `date` int, `time` int, `x` int);INSERT INTO passes    (`id`, `u_id`, `date`, `time`, `x`)VALUES    (1, 1, 20200806, 0900, 60),    (2, 2, 20200806, 0905, 60),    (3, 3, 20200806, 0910, 61),    (4, 1, 20200806, 1015, 62),    (5, 1, 20200806, 1830, 61),    (6, 3, 20200807, 0915, 61),    (7, 1, 20200807, 0920, 62),    (8, 2, 20200807, 1820, 63),    (9, 1, 20200807, 1835, 59);MySQL 查询：Select SUB1.TheDate,SUB1.firstTime,x1.x as firstX,SUB1.lastTime,x2.x as lastX from (select      passes.date as TheDate,      min(passes.time) as firstTime,      max(passes.time) as lastTimefrom passeswhere  passes.u_id = 1group by passes.date) AS SUB1join passes as x1 on x1.date = SUB1.TheDateand x1.time = SUB1.firstTimejoin passes as x2 on x2.date = SUB1.TheDateand x2.time = SUB1.lastTime结果如下：TheDate     firstTime   firstX  lastTime    lastX20200806    900         60      1830        6120200807    920         62      1835        59