正文
count 汇总函数
理逻辑：
•
找出在某个时间区间里面有消费记录的会员编号
•
为会员编号随机的排序
•
为排好序的会员列表添加序号
•
取出序号小于总会员数的 30%
/*
dimMemberID：会员 ID
dimDateID：下单日期
dimShopID：门店 ID
取出门店、会员的 ID 号，并使用 group by 进行去重操作
*/
写代码：
select dimShopID
, dimMemberID
from dw.fct_sales
where dimDateID between 20170602 and 20170602
group by dimShopID
,dimMemberID
select dimShopID
, dimMemberID
, row_number() over(partition by dimShopID order by random() ) as rn
, count(dimMemberID) over(partition by dimShopID ) as member_cnt
from (
select dimShopID , dimMemberID
from dw.fct_sales
where dimDateID between 20170602 and 20170602
group by dimShopID ,dimMemberID
) as q1
上面这段有点复杂了，让我一一道来。
row_number 的功能是给表里每一个记录加一个序号，举个例子
