Hadoop离线_hive求转化率(漏斗模型)

---

1.例表

select * from dw_oute_numbs

+---------------------+----------------------+--+
| dw_oute_numbs.step  | dw_oute_numbs.numbs  |
+---------------------+----------------------+--+
| step1               | 1029                 |
| step2               | 1029                 |
| step3               | 1028                 |
| step4               | 1018                 |
+---------------------+----------------------+--+

2.求取每一步相对于上一步的转化率

SELECT t.rnnumbs/t.rrnumbs AS rate
FROM (
SELECT
rn.step AS rnstep,
rn.numbs AS rnnumbs,
rr.step AS rrstep,
rr.numbs AS rrnumbs
FROM dw_oute_numbs rn
INNER JOIN dw_oute_numbs rr
WHERE rr.step = 'step1' ) t;

3.求取每一步相对于第一步的转化率

SELECT t.rrnumbs/t.rnnumbs AS rate
FROM (
SELECT
rn.step AS rnstep,
rn.numbs AS rnnumbs,
rr.step AS rrstep,
rr.numbs AS rrnumbs
FROM dw_oute_numbs rn
INNER JOIN dw_oute_numbs rr
WHERE CAST(substr(rn.step,5,1) AS INT) = CAST(SUBSTR(rr.step,5,1) AS INT) - 1 ) t;

以上用到的两个函数：
1. sbustr(String str,int num,int len)：表示把str从第num个字符开始切割，共切割len个字符
2.CAST(arg1 AS arg2)：这是个转换函数，arg1表示要转换的数据，arg2表示目标类型