-> SELECT customer_id, count(*) num_rentals,
-> row_number() over (order by count(*) desc) row_number_rnk,
-> rank() over (order by count(*) desc) rank_rnk,
-> dense_rank() over (order by count(*) desc) dense_rank_rnk
-> FROM rental
-> GROUP BY customer_id
-> ORDER BY 2 desc;
4.2.5. 生成多个排名
4.2.5.1. 通过在over子句中加入partition by子句来实现
4.2.5.2. sql
SELECT customer_id, rental_month, num_rentals,
rank_rnk ranking
FROM
(SELECT customer_id,
monthname(rental_date) rental_month,
count(*) num_rentals,
rank() over (partition by monthname(rental_date)
order by count(*) desc) rank_rnk
FROM rental
GROUP BY customer_id, monthname(rental_date)
) cust_rankings
WHERE rank_rnk <= 5
ORDER BY rental_month, num_rentals desc, rank_rnk;
-> SELECT monthname(payment_date) payment_month,
-> amount,
-> sum(amount)
-> over (partition by monthname(payment_date)) monthly_total,
-> sum(amount) over () grand_total
-> FROM payment
-> WHERE amount >= 10
-> ORDER BY 1;
4.3.5. mysql
-> SELECT monthname(payment_date) payment_month,
-> sum(amount) month_total,
-> round(sum(amount) / sum(sum(amount)) over ()
-> * 100, 2) pct_of_total
-> FROM payment
-> GROUP BY monthname(payment_date);
4.3.6. mysql
-> SELECT monthname(payment_date) payment_month,
-> sum(amount) month_total,
-> CASE sum(amount)
-> WHEN max(sum(amount)) over () THEN 'Highest'
-> WHEN min(sum(amount)) over () THEN 'Lowest'
-> ELSE 'Middle'
-> END descriptor
-> FROM payment
-> GROUP BY monthname(payment_date);
4.3.7. 使用partition by子句来为分析函数定义数据窗口,允许按照公共值对行进行分组
4.3.8. 流水式总和
4.3.8.1. mysql
-> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> sum(sum(amount))
-> over (order by yearweek(payment_date)
-> rows unbounded preceding) rolling_sum
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
4.3.9. 流水式平均值
4.3.9.1. mysql
-> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> avg(sum(amount))
-> over (order by yearweek(payment_date)
-> rows between 1 preceding and 1 following) rolling_3wk_avg
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
4.3.10. lag和lead
4.3.10.1. 涉及将一行中的值与另一行进行比较
4.3.10.2. mysql
-> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> lag(sum(amount), 1)
-> over (order by yearweek(payment_date)) prev_wk_tot,
-> lead(sum(amount), 1)
-> over (order by yearweek(payment_date)) next_wk_tot
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
4.3.10.3. mysql
-> SELECT yearweek(payment_date) payment_week,
-> sum(amount) week_total,
-> round((sum(amount) - lag(sum(amount), 1)
-> over (order by yearweek(payment_date)))
-> / lag(sum(amount), 1)
-> over (order by yearweek(payment_date))
-> * 100, 1) pct_diff
-> FROM payment
-> GROUP BY yearweek(payment_date)
-> ORDER BY 1;
5. 列值拼接
5.1. 处理数据窗口中的行组
5.2. group_concat函数
5.2.1. 用于将一组列值转换为单个分隔字符串
5.2.2. 一种将结果集反规范化(denormalize)以生成XML或JSON文档的便捷方法
5.2.3. mysql
-> SELECT f.title,
-> group_concat(a.last_name order by a.last_name
-> separator ', ') actors
-> FROM actor a
-> INNER JOIN film_actor fa
-> ON a.actor_id = fa.actor_id
-> INNER JOIN film f
-> ON fa.film_id = f.film_id
-> GROUP BY f.title
-> HAVING count(*) = 3;