問題描述

如題呢。有一張表記錄了一些數(shù)據(jù)和數(shù)據(jù)的時間戳

問題解答

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 行記錄)

先把數(shù)據(jù)按月份劃分窗口，再把每個月份窗口內(nèi)部排序，最后選出序號為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_windowswhere 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));回答2：

沒有 mysql 環(huán)境, 在 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 t1right 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 t1right 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

具體根據(jù)自己字段調(diào)整

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

大概是這樣的感覺，然而我并不知道怎么從時間中提取出月份，自行解決吧。

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

用時間函數(shù)轉(zhuǎn)換 可以的