按日期分组并显示 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 pwhere      p.id=1group 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

     ) p

where p.id=1

group 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_x

from passes p

where p.id = 1

group 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 = 1

window 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 lastTime

from passes

where

  passes.u_id = 1

group by passes.date) AS SUB1

join passes as x1 

on x1.date = SUB1.TheDate

and x1.time = SUB1.firstTime

join passes as x2 

on x2.date = SUB1.TheDate

and x2.time = SUB1.lastTime

结果如下：

TheDate     firstTime   firstX  lastTime    lastX

20200806    900         60      1830        61

20200807    920         62      1835        59