我有像這樣的桌子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 的值。行：
3 回答
ibeautiful
TA貢獻1993條經驗 獲得超6個贊
一種方法使用條件聚合：
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;
繁花不似錦
TA貢獻1851條經驗 獲得超4個贊
我將使用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);
jeck貓
TA貢獻1909條經驗 獲得超7個贊
我能夠通過創建子查詢來重現您的結果。子查詢按日期對值進行分組并返回第一次和最后一次。
通過確定第一次和最后一次的日期，我做了兩次連接，一次是為了獲取第一個 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
- 3 回答
- 0 關注
- 163 瀏覽
添加回答
舉報
0/150
提交
取消