电商

1、互联网公司Order表相关常见Hql分析 1.1、 管理报表-订单基本统计信息(日粒度) 查看每个城市每天完成订单数，取消订单数，下单订单数，下单用户数。

select city_id,sum(case when order_status=5 then 1 else 0 end) as cnt_ord_succ_d,
sum(case when order_status=3 then 1 else 0 end) as cnt_ord_cacel_d,
sum(1) as cnt_ord_d,
count(distinct CUST_ID) as cnt_ord_user
FROM ods_order WHERE dt='20151010'
group by city_id;

1.2 运营团队-订单数据信息(活动分析) 1.3 商品复购率Hql分析 需求列出的商品的7日,15日,30复购率，目的了解这几款商品的周期.计算口径:当日购买部分商品的用户数/7日重复购买此商品的用户数。每天查看每个城市每个商品当日购买用户数，7日、15日、30日复购率。 当天订单 t1

SELECT o.order_date AS atdate,o.city_id,
o.cust_id,og.goods_id
FROM ods_order o INNER JOIN ods_order_goods og
ON o.order_id=og.order_id 
AND o.ORDER_STATUS = 5
AND og.source_id=1
AND o.dt = '20151010' 

所有订单 t2

SELECT 
o.order_date AS atdate,
o.city_id,
o.cust_id,
og.goods_id,
og.goods_name
FROM ods_order o INNER JOIN ods_order_goods og
ON o.order_id=og.order_id 
AND o.ORDER_STATUS = 5
AND og.source_id=1

两者通过join, 获取与当天时间差 t3

SELECT t1.atdate,t1.city_id,t1.cust_id,t1.goods_id,
DATEDIFF(t2.atdate, t1.atdate) days    -- 时间差
FROM  t1 INNER JOIN t2 
ON t1.cust_id=t2.cust_id AND t1.goods_id=t2.goods_id

复购率

SELECT t3.atdate AS cdate,t3.city_id,t3.goods_id,
COUNT(DISTINCT CASE WHEN days=0 THEN t3.cust_id END) AS cnt_buy_cust_d,
COUNT(DISTINCT CASE WHEN days>0 AND days0 AND days0 AND days0 AND days0 AND days0 AND days='2015/10/01' AND t.COMPLETION_DATE='2015/10/01' AND t.COMPLETION_DATE='2015/10/01' AND t.COMPLETION_DATE100
AND t.ORDER_STATUS=5 group by COMPLETION_DATE;

2 互联网公司User相关常见Hql分析 2.1用户总体数据信息Hql分析(历史累计)

求每个用户累计订单数，累计应付金额

• NVL(expr1, expr2)： 空值转换函数；
  • 1、如果expr1为NULL，返回值为 expr2，否则返回expr1。
  • 2、适用于数字型、字符型和日期型，但是 expr1和expr2的数据类型必须为同类型。
• ods_cumstomer中amount_sum记录 累计金额 ， order_cnt记录累计订单数，

select nvl(t1.cust_id,t2.cust_id), 
nvl(t2.order_cnt,0)+nvl(t1.order_cnt,0) as order_cnt,
nvl(t2.amount_sum,0)+nvl(t1.amount_sum,0) as amount_sum
from ods_customer t1
full outer join (
select cust_id,count(1) as order_cnt,sum(payable_amount) as amount_sum from 
ods_order where 
dt='20151010'   -- 分区
and order_status=5
group by cust_id
) t2 on t1.cust_id=t2.cust_id and t1.dt='20151010' limit 100;

2.2新用户统计信息Hql分析(日粒度)

select count(1) from ods_customer
where dt='20151210' and  
from_unixtime(unix_timestamp(register_time,'yyyy/MM/dd HH:mm'),'yyyyMMdd')='20140610';

2.3求5,6月各个渠道带来的新用户，以此来考核运营部门的kpi

select cust_source, -- 用户来源：微信、微博、IOS、Android
count(1) from ods_customer
where dt=20151210 
and from_unixtime(unix_timestamp(register_time,'yyyy/MM/dd HH:mm'),'yyyyMMdd')>='20140501'
and from_unixtime(unix_timestamp(register_time,'yyyy/MM/dd HH:mm'),'yyyyMMdd')