1.随机获取表中100条数据
select * from table order by rand() limit 100;
2.统计表中每10分钟的pv量
select floor(unix_timestamp(window_start)/600),sum(pv_num) from table
where window_start <= date_add(now(),interval -20 minute) and
window_start >= date_add(now(),interval -30 minute)
group by floor(unix_timestamp(window_start)/600)
select FROM_UNIXTIME(floor(unix_timestamp(record_time)/600)600,'%Y-%m-%d %H:%i:%s'),count(user_no) rk from table
group by FROM_UNIXTIME(floor(unix_timestamp(record_time)/600)600,'%Y-%m-%d %H:%i:%s')
having rk > 1
3.计算上下两行相减
select *,LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE) Next_Date From table
SELECT *,CASE WHEN LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE) IS NULL
OR
DATEDIFF(DAY,LAG(CALLBACK_DATE) OVER
(PARTITION BY UID ORDER BY CALLBACK_DATE),CALLBACK_DATE) < 7
THEN '不计费'
ELSE '计费' END AS CHARGE
FORM table
ORDER BY UID,CALLBACK_DATE
4.count开窗函数
select t.Docnum
from (
select * ,count(1) over (partition by Docnum) AS RN
from table
) t
where t.status = 'FULL'
and t.RN = 1
5.mysql5.6实现分组，排序获取第一条数据
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
