求教一个sql，取出所有半年内的每个月的第一条符合条件的记录

Question

如题呢。有一张表记录了一些数据和数据的时间戳

Answer 1

select * from tbl;

       tstamp        | data
---------------------+------
 2016-02-03 00:00:00 |  200
 2016-02-04 00:00:00 |  300
 2015-07-06 00:00:00 |  202
 2016-09-01 00:00:00 |  300
 2015-07-06 00:00:00 |  324
 2015-07-15 00:00:00 |  299
(6 行记录)

先把数据按月份划分窗口，再把每个月份窗口内部排序，最后选出序号为1的记录。

select tstamp, data from
  (select tstamp, data, row_number() over
    (partition by date_trunc('month', tstamp) order by tstamp) as row
  from tbl) as tbl_windows
where row = 1;

       tstamp        | data
---------------------+------
 2015-07-06 00:00:00 |  202
 2016-02-03 00:00:00 |  200
 2016-09-01 00:00:00 |  300
(3 行记录)

如果表有自增的id列，还可以用更简单的

select tstamp, data from tbl where id in
  (select min(id) from tbl group by date_trunc('month', tstamp));

Answer 2

沒有 mysql 環境, 在 sqlserver 給試了下

create table td(
tstamp datetime,
data int
);
    
insert into td (tstamp, data) values
('2016-02-03 00:00:00', 200),
('2016-02-04 00:00:00', 300),
('2015-07-06 00:00:00', 202),
('2016-09-01 00:00:00', 300),
('2015-07-06 00:00:00', 324),
('2015-07-15 00:00:00', 299),
('2016-03-05 00:00:00', 499);

select * from td as t1
right join (
select
 convert(varchar(7), tstamp, 126) as ts1,
 min(tstamp) as ts2 from td
 where tstamp between '2016-01-01' and '2016-07-01'
 group by convert(varchar(7), tstamp, 126)
) as t2 on t1.tstamp=t2.ts2

tstamp                   data         ts1      ts2                      
------------------------------------------------------------------------
2016-02-03 00:00:00.0    200          2016-02  2016-02-03 00:00:00.0    
2016-03-05 00:00:00.0    499          2016-03  2016-03-05 00:00:00.0    

下面是翻譯成 mysql 的語句(未測試)

select * from td as t1
right join (
 select
  date_format(tstamp, '%y-%m') as ts1,
  min(tstamp) as ts2 from td
  where tstamp between '2016-01-01' and '2016-07-01'
  group by date_format(tstamp, '%y-%m')
) as t2 on t1.tstamp=t2.ts2

具體根據自己字段調整

Answer 3

select record from dataset where time.month in ("Jan","Feb",..."Nov") limit 1

大概是这样的感觉，然而我并不知道怎么从时间中提取出月份，自行解决吧。

Answer 4

select `data` as data,MONTH(FROM_UNIXTIME(`dateline`)) AS M from table where dateline<半年 group by M ;

Answer 5

用时间函数转换 可以的