以前做过几年的java编程,后来慢慢转型做架构设计和项目管理,对编程和算法也慢慢的疏远了
这段时间有好多网友询问一些sql题,觉得很有意思,也跟着大家一起做sql题,一时做的兴起,
还慢慢的上瘾了,有sql题必做。
今天在网上看到个sql题,觉得很过瘾,以前也做不过,不过没有记下来,今天有做过一次,
特意记下
题目如下:
数据库表结构如下:
id datetime value
1 2009-08-06 08:30 20
1 2009-08-06 08:32 23
1 2009-08-06 08:34 22
1 2009-08-06 08:36 25
1 2009-08-06 08:39 29
1 2009-08-06 08:40 23
1 2009-08-06 08:44 30
1 2009-08-06 08:47 31
1 2009-08-06 08:51 34
1 2009-08-06 08:53 36
2 2009-08-06 08:32 40
2 2009-08-06 08:34 42
2 2009-08-06 08:37 43
2 2009-08-06 08:39 45
2 2009-08-06 08:45 47
1 2009-08-07 08:30 30
1 2009-08-07 08:34 32
1 2009-08-07 08:37 33
1 2009-08-07 08:41 34
1 2009-08-07 08:44 36
1 2009-08-07 08:48 37
1 2009-08-07 08:50 39
1. 按id来搜索前一天的所有结果,如搜索8.6日id=1的所有数据;
2. 以条件1为前提,以5分钟为间隔取得所有数据,如08:30,08:35,08:40,表中不足整5分钟的取最临近的时间数据;
例如
id datetime value
1 2009-08-06 08:30 20
1 2009-08-06 08:40 23
1 2009-08-06 08:44 30
1 2009-08-06 08:51 34
这样每5分钟取一个数据点,表中不足整5分钟的取最临近的时间数据。
第一个没有多大,难度
马上拿下
做题主要准备数据麻烦,以前总是做个表,然后做完,删除掉,看有的网友用with 。。as的方法,不错,我也学会了。
with table_a as
(select 1 id, to_date('2009-08-08 08:30', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:32', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:34', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:36', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:39', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:40', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:44', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:47', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:51', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-08 08:53', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 2 id, to_date('2009-08-08 08:32', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 2 id, to_date('2009-08-08 08:34', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 2 id, to_date('2009-08-08 08:37', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 2 id, to_date('2009-08-08 08:39', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 2 id, to_date('2009-08-08 08:45', 'yyyy-mm-dd hh24:mi') datetime, 20 value from dual
union
select 1 id, to_date('2009-08-07 08:30', 'yyyy-mm-dd hh24:mi') datetime, 70 value from dual
union
select 1 id, to_date('2009-08-08 08:00', 'yyyy-mm-dd hh24:mi') datetime, 90 value from dual
union
select 1 id, to_date('2009-08-08 08:04', 'yyyy-mm-dd hh24:mi') datetime, 10 value from dual
union
select 1 id, to_date('2009-08-08 08:09', 'yyyy-mm-dd hh24:mi') datetime, 30 value from dual
union
select 1 id, to_date('2009-08-08 08:10', 'yyyy-mm-dd hh24:mi') datetime, 50 value from dual
union
select 1 id, to_date('2009-08-07 08:16', 'yyyy-mm-dd hh24:mi') datetime, 80 value from dual)
----第一题
select * from table_a where id = '1' and trunc(datetime+1,'dd')=trunc(sysdate,'dd');
第二题,稍微有点难度,想了想,觉得出算法,采样就是建采样模型的过程。以5分钟采样,也就是按60分钟,每5分钟见模型,
这样的话也就是group by 小时, 5分钟为单位,这里就可以得到采样数据了。然后这里还有个比较麻烦的也就是,有要求如果
没有匹配的,选择最接近的,我这里的方式就是按01-05,06-10,这样的方式来进行范围选择,然后最接近的也就是其中最大
的,算法基本上出来了,那就开始吧。用oracle特定的函数解决吧,就不需要max了
下面是我给出了的sql
select id, to_char(datetime, 'yyyy-mm-dd hh24:mi'), value from (select a.*, row_number() over(partition by trunc(datetime, 'hh24'), trunc((to_number(to_char(datetime, 'mi'))+4)/5) order by mod((to_number(to_char(datetime, 'mi'))+4), 5) desc) rn from table_a a where id = 1 and trunc(datetime+1,'dd')=trunc(sysdate,'dd')) where rn=1;
这里几个关键的地方
partition by trunc(datetime, 'hh24'), trunc((to_number(to_char(datetime, 'mi'))+4)/5)
这里是采样的分组,小时和5分钟的单位量
order by mod((to_number(to_char(datetime, 'mi'))+4), 5) desc
按余数降序排序,记下row-number,1就是最近的样了
这里是特定的oracle里的over和row_number解决的,不过不是标准sql,不留遗憾,我们用mysql实现一下,标准sql改如何修改,当然每种数据库里,字符和时间处理的function都又不同,这个迁移的时候,到没有太大问题,可以见同名同参数的function来替代就可以了,关键的移植是把上面特有的sql用group by来做,
select id, value, datetime from table_a a where exists (select 1 from (select max(datetime) from table_a where id = 1 and ... group by trunc(datetime, 'hh24'), div((to_number(to_char(datetime, 'mi'))+4), 5)) b where a.datetime=b.datetime);
完成,这里需要在mysql里加上trunc, div这些funtin就可以ile。