如何进行随机抽奖

抽奖逻辑说明

在某个时段内，会员在超市内下单，就有机会得到超市派送的惊喜小礼物

业务说明和价值说明

• 在门店经营过程中，经常在一些节假日里面做一些抽奖活动，为门店带来线上或者线下的客流。
• 客流的增加很容易带动销售额的上涨。
• 抽奖也是的超市常用的促销手段。

超市的主要商业场景

随着智能手机的普及，超市越来越意识到 APP 在触达用户方面的优势，所以纷纷推出了自己的微信小程序或者独立的 APP。利用这种手动，超市可以更加快捷、高效的做一些线上的活动，例如，有著名的双 11、双 12 等等。其中抽奖就是一个重要玩法。这种玩法更加的公平，减少了人为的干预。另外，这种线上的运营手法，可以增加超市的存在感，增加用户的粘性。

商业知识点思考

这个涉及到了线上和线下运营的知识，早前互联网没有这样普及的时候，商家为了吸引客流，要兴师动众的请歌手、二人转到商城表演，这是利用了名人效应。也可以在黄金时段，电视上插播广告，这是利用了广播电视的手段。来到了移动互联网的时代，商家可以通过手机这种高效的手段触达用户，吸引客流。

SQL 知识点

• random() 随机函数
• count、row_number 分析函数

逻辑说明

• 找出在某个时间区间里面有消费记录的会员编号
• 为会员编号随机的排序
• 为排好序的会员列表添加序号
• 取出序号小于总会员数的 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 的功能是给表里每一个记录加一个序号，举个例子

会员 ID	row_number
A	1
B	2
C	3
D	4

如上面的表格所示，第一列是会员的 ID，第二列是 row_number 生成的序号，就像我们到店里面吃麻辣烫，我挑完菜品以后，收银员会给我们一个号牌，这个号牌有个特点，就是不能重号，因为如果当店员喊 23 号来取餐，来了两个人拿着同样的号牌来取餐，这将成为一个事故现场。我们相当于每个会员一个号牌，我们根据号牌来找到幸运顾客，例如，我们可以直接说号码小于 30 的顾客是幸运儿。那么我们怎么做到每个顾客拿到的前三十个号牌的概率是相同的呢？

random() 函数帮我们解决了这个问题，那么 random 是何许人也呢？请看下面的例子：

会员 ID	random
A	0.1
B	0.2
C	0.3
D	0.4

当扫描到表中其中一行记录的时候，random 会随机的生成一个小于 1 的小数，由于每个记录生成的小数是随机的，那么 A、B、C、D 拿到 0.1 的概率也是相同。这就达到公平了。

啰嗦了这么多，还是讲 row_number + random 的用法吧

row_number() over(partition by dimShopID order by random() ),其中 partition by dimShopID 是限定不同的店下面要从 1 开始生成序号，例如：

店编	会员 ID	row_number
店1	A	1
店1	B	2
店2	C	1
店2	D	2

从上面的例子可以看到，当从店1变到店2的时候，row_number 会从 1 开始排号。

然后 order by random() 会根据随机生成的小数排序。

count(dimMemberID) over(partition by dimShopID ) as member_cnt 这一句的意思是统计不同门店里面有多少个会员

通过以后步骤，我们会得到每家门店的会员数、随机排序的会员列表，接着我们可以计算
出每家门店的 30% 的会员数量(设为 a)，然后我们再序号把小于 a 的会员过滤出来，大功告成了。最后的结果如下所示：

select dimShopID
  from (
    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    
       ) as q2
where rn <= member_cnt*0.3