1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG 2、使用PARTITION BY语句,使用一个或者多个原始数据类型的列 3、使用PARTITION BY与ORDER BY语句,使用一个或者多个数据类型的分区或者排序列 4、使用窗口规范,窗口规范支持以下格式:
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
在聚合函数(SUM、COUNT and AVG)中,支持distinct,但是在ORDER by或者窗口限制不支持
count(distinct a) over (partition by c)
COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
SELECT rank() OVER (ORDER BY sum(b))
FROM T
GROUP BY a;
-- count、sum、min、max、avg
select
user_id,
user_type,
sales,
--默认为从起点到当前行,sales相等的是不分先后顺序的,所以第一个值为2,还有17=5+5+7
sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc) AS sales_1,
--从起点到当前行,结果与sales_1不同。像这种情况,就是有序的,即1,2,4,7,12,17,23;
sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sales_2,
--当前行+往前3行
sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS sales_3,
--当前行+往前3行+往后1行
sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS sales_4,
--当前行+往后所有行
sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sales_5,
--分组内所有行
SUM(sales) OVER(PARTITION BY user_type) AS sales_6
from
order_detail
order by
user_type,
sales,
user_id;
first_value与last_value
select
user_id,
user_type,
ROW_NUMBER() OVER(PARTITION BY user_type ORDER BY sales) AS row_num,
first_value(user_id) over (partition by user_type order by sales desc) as max_sales_user,
first_value(user_id) over (partition by user_type order by sales asc) as min_sales_user,
last_value(user_id) over (partition by user_type order by sales desc) as curr_last_min_user,
last_value(user_id) over (partition by user_type order by sales asc) as curr_last_max_user
from
order_detail;
lead与lag
select
user_id,device_id,sales,
lead(device_id) over (order by sales) as default_after_one_line,
lag(device_id) over (order by sales) as default_before_one_line,
lead(device_id,2) over (order by sales) as after_two_line,
lag(device_id,2,'abc') over (order by sales) as before_two_line
from
order_detail;
| user_id | device_id | default_after_one_line | default_before_one_line | after_two_line | before_two_line |
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
| qibaqiu | fds | fdsfagwe | NULL | 543gfd | abc |
| liiu | fdsfagwe | 543gfd | fds | f332 | abc |
| lisi | 543gfd | f332 | fdsfagwe | dfsadsa323 | fds |
| wangshi | f332 | dfsadsa323 | 543gfd | hfd | fdsfagwe |
| zhangsa | dfsadsa323 | hfd | f332 | 65ghf | 543gfd |
| liwei | hfd | 65ghf | dfsadsa323 | fds | f332 |
| wanger | 65ghf | fds | hfd | dsfgg | dfsadsa323 |
| qishili | fds | dsfgg | 65ghf | 543gdfsd | hfd |
| lilisi | dsfgg | 543gdfsd | fds | NULL | 65ghf |
| wutong | 543gdfsd | NULL | dsfgg | NULL | fds |
+----------+-------------+-------------------------+--------------------------
rank、row_number、dense_rank
select
user_id,user_type,sales,
RANK() over (partition by user_type order by sales desc) as r,
ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
order_detail;
| user_id | user_type | sales | r | rn | dr |
+----------+------------+--------+----+-----+-----+--+
| wutong | new | 6 | 1 | 1 | 1 |
| qishili | new | 5 | 2 | 2 | 2 |
| lilisi | new | 5 | 2 | 3 | 2 |
| wanger | new | 3 | 4 | 4 | 3 |
| zhangsa | new | 2 | 5 | 5 | 4 |
| qibaqiu | new | 1 | 6 | 6 | 5 |
| liiu | new | 1 | 6 | 7 | 5 |
| liwei | old | 3 | 1 | 1 | 1 |
| wangshi | old | 2 | 2 | 2 | 2 |
| lisi | old | 1 | 3 | 3 | 3 |
ntile
select
user_type,sales,
--分组内将数据分成2片
NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2,
--分组内将数据分成3片
NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3,
--分组内将数据分成4片
NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4,
--将所有数据分成4片
NTILE(4) OVER(ORDER BY sales) AS all_nt4
from
order_detail
order by
user_type,
sales
求取sale前20%的用户ID
select
user_id
from
(
select
user_id,
NTILE(5) OVER(ORDER BY sales desc) AS nt
from
order_detail
)A
where nt=1;
--wutong
--qishili
CUME_DIST、PERCENT_RANK
select
user_id,user_type,sales,
--没有partition,所有数据均为1组
CUME_DIST() OVER(ORDER BY sales) AS cd1,
--按照user_type进行分组
CUME_DIST() OVER(PARTITION BY user_type ORDER BY sales) AS cd2
from
order_detail;
select
user_type,sales,
--分组内总行数
SUM(1) OVER(PARTITION BY user_type) AS s,
--RANK值
RANK() OVER(ORDER BY sales) AS r,
PERCENT_RANK() OVER(ORDER BY sales) AS pr,
--分组内
PERCENT_RANK() OVER(PARTITION BY user_type ORDER BY sales) AS prg
from
order_detail;
select
user_type,
sales,
count(user_id) as pv,
GROUPING__ID
from
order_detail
group by
user_type,sales
GROUPING SETS(user_type,sales)
ORDER BY
GROUPING__ID;
select
user_type,
sales,
count(user_id) as pv,
GROUPING__ID
from
order_detail
group by
user_type,sales
GROUPING SETS(user_type,sales,(user_type,sales))
ORDER BY
GROUPING__ID;
select
user_type,
sales,
count(user_id) as pv,
GROUPING__ID
from
order_detail
group by
user_type,sales
WITH CUBE
ORDER BY
GROUPING__ID;
select
user_type,
sales,
count(user_id) as pv,
GROUPING__ID
from
order_detail
group by
user_type,sales
WITH ROLLUP
ORDER BY
GROUPING__ID;
user_type,sales
WITH CUBE
ORDER BY
GROUPING__ID;
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/192894.html原文链接:https://javaforall.cn