問(wèn)題描述

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

問(wèn)題解答

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ù)按月份劃分窗口，再把每個(gè)月份窗口內(nèi)部排序，最后選出序號(hào)為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列，還可以用更簡(jiǎn)單的

select tstamp, data from tbl where id in (select min(id) from tbl group by date_trunc(’month’, tstamp));回答2：

沒(méi)有 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 的語(yǔ)句(未測(cè)試)

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

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

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

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