1.多行合并
多行合并常用于做区间统计,通过定义一定的金额区级,将上亿的记录降维为不同区间内总数。概括来说就是多映射到一。
典型场景:
基于用户交易天流水,计算每天不同金额段的金额笔数。
如用户的天交易流水表结构如上,需要计算出交易额在0-100,100-200,200-300,大于300几个区级的笔数,
CREATE VIEW t_deal_tmp_view_1 AS
SELECT
CASE
WHEN rcv_amount <= 100 THEN 1
WHEN rcv_amount <= 200 THEN 2
WHEN rcv_amount <= 300 THEN 3
ELSE 4 END AS amount_range,
receiver
FROM t_transfer_info
SELECT
amount_range,
COUNT(receiver) AS cnt
FROM t_deal_tmp_view_1
GROUP BY amount_range
DROP VIEW t_deal_tmp_view_1
为什么不使用下面这种写法
SELECT
CASE
WHEN rcv_amount <= 100 THEN 1
WHEN rcv_amount <= 200 THEN 2
WHEN rcv_amount <= 300 THEN 3
ELSE 4 E