case when用法
来源:互联网 发布:跟兄弟连学php 当当网 编辑:程序博客网 时间:2024/06/07 04:26
1、划分时间段
select deviceid,
case when server_time >=20160825000000 and server_time <20160825010000 then 1
when server_time >=20160825010000 and server_time <20160825020000 then 2
when server_time >=20160825020000 and server_time <20160825030000 then 3
when server_time >=20160825030000 and server_time <20160825040000 then 4
when server_time >=20160825040000 and server_time <20160825050000 then 5
when server_time >=20160825050000 and server_time <20160825060000 then 6
when server_time >=20160825060000 and server_time <20160825070000 then 7
when server_time >=20160825070000 and server_time <20160825080000 then 8
when server_time >=20160825080000 and server_time <20160825090000 then 9
when server_time >=20160825090000 and server_time <20160825100000 then 10
when server_time >=20160825100000 and server_time <20160825110000 then 11
when server_time >=20160825110000 and server_time <20160825120000 then 12
when server_time >=20160825120000 and server_time <20160825130000 then 13
when server_time >=20160825130000 and server_time <20160825140000 then 14
when server_time >=20160825140000 and server_time <20160825150000 then 15
when server_time >=20160825150000 and server_time <20160825160000 then 16
when server_time >=20160825160000 and server_time <20160825170000 then 17
when server_time >=20160825170000 and server_time <20160825180000 then 18
when server_time >=20160825180000 and server_time <20160825190000 then 19
when server_time >=20160825190000 and server_time <20160825200000 then 20
when server_time >=20160825200000 and server_time <20160825210000 then 21
when server_time >=20160825210000 and server_time <20160825220000 then 22
when server_time >=20160825220000 and server_time <20160825230000 then 23
when server_time >=20160825230000 and server_time <20160826000000 then 24
end timerange
from base.transfer_dis
2、统计数据量
select type,count(distinct deviceid) num
from(
select deviceid,
case when title like '%.apk' then "apk"
when title like '%.mp4' then "mp4"
when title like '%.mp3' then "mp3"
when title like '%.jpg' then "jpg"
when title like '%.m4a' then "m4a"
when title like '%.gif' then "gif"
when title like '%.aac' then "aac"
when title like '%.jpg' then "jpg"
end type
from base.transfer_dis
where concat(year,month,day) = 20160825
and country = "中国"
and server_time >= 20160825200000
and server_time <= 20160825230000) tmp
group by type
3、直接统计数据量
set mapred.job.priority=very_high;
set mapred.reduce.tasks=40;
select count(distinct case when title like '%.apk' then deviceid end) apk,
count(distinct case when title like '%.mp4' then deviceid end) mp4,
count(distinct case when title like '%.mp3' then deviceid end) mp3,
count(distinct case when title like '%.jpg' then deviceid end) jpg,
count(distinct case when title like '%.m4a' then deviceid end) m4a,
count(distinct case when title like '%.gif' then deviceid end) gif,
count(distinct case when title like '%.aac' then deviceid end) aac,
count(distinct case when title like '%.jpg' then deviceid end) jpg
from base.transfer_dis
where concat(year,month,day) = 20160825
and country = "中国"
and server_time >= 20160825200000
and server_time <= 20160825230000
- MSSQL Case when用法
- case when 的用法
- case when的用法
- oracle case when 用法
- case when的用法
- case when用法
- case when 用法
- Case when用法
- SQL ----case when 用法
- Oracle case when 用法
- Oracle case when用法
- case when 用法
- sql case when 用法
- oracle case when 用法
- oracle case when 用法
- Case when的用法
- oracle case when 用法
- Oracle case when用法
- java 基础之网络编程
- HDU 3466 Proud Merchants【01dp】
- NS3系列—4———NS3中文教程5:Tweaking NS3
- #Day2 C++
- Leetcode: Median of Two Sorted Arrays
- case when用法
- CSS3的REM设置字体大小
- 教你如何关掉NEXUS5手机OTA升级提醒
- 嵌入式 - 虚拟机、开发板与主机Ping不通问题解决方法
- RabbitMQ概念及环境搭建 -- RabbitMQ cluster
- C++ 11多线程编程--std::lock_guard类
- NS3系列—5———NS3的两个例子
- 杭电1434 优先队列
- zxing