基于规则生成促销商品清单的SQL实现技术问询

业务规则 #

1. 从#productdetail表筛选满足available=1或available=2、expected<='2019-08-05'且isnotsellable=0的商品；
2. 关联#product表，通过pid与id匹配，使用coalesce函数获取vendor_id；
3. 以上述vendor_id关联#vendor表获取vendorname；
4. 从#asin表筛选disable=0的记录，为步骤1选中商品获取最新lastconfirmedasin；
5. 基于步骤1商品的lastconfirmedasin，从#pageviews表计算日均页面浏览量：若同时存在datetype='day'和'month'数据，取'day'数据按sum(noofpageviews)/天数计算；若仅存'month'数据，按(sum(noofpageviews)*月数据行数)/30计算；
6. 在#promo表按供应商维度计算平均促销折扣率，公式为avg(avg((pre_promo_cost-promo_cost)/promo_cost*100))；
7. 成本取值：若当前日期（示例2019-08-16）在#promo表promo_start与promo_end区间内，取pre_promo_cost，否则取#productdetail表的cost；
8. 按供应商维度，根据power值（pageviewsperday*price）排序，值越高优先级越高。

表结构及测试数据 #

create table #productdetail ( itemid int, available int, expected date, isnotsellable int, pid int, vendor_id varchar(50), cost float, price float)
insert into #productdetail values ('123','1',NULL,'1','1','201','180','200'), ('125','2','08/05/2019','0','1','NULL','40','60'), ('127','1',NULL,'0','1','201','60','80'), ('129','2',NULL,'0','2','203','80','100'), ('131','1',NULL,'0','2','203','70','90'), ('133','1',NULL,'1','2','203','90','110'), ('135','1',NULL,'0','7','206','110','130'), ('137','1',NULL,'0','8','207','120','140'), ('139','1',NULL,'0','8','207','30','50'), ('141','1',NULL,'0','8','207','40','60'), ('143','1',NULL,'0','11','210','60','80'), ('145','1',NULL,'0','11','210','70','90'), ('147','1',NULL,'1','11','210','50','70'), ('149','1',NULL,'0','11','210','20','40'), ('151','1',NULL,'0','15','211','30','50'), ('153','1',NULL,'0','16','NULL','40','60'), ('155','2','08/29/2019','1','15','211','60','80'), ('157','2','08/04/2019','1','18','216','30','50'), ('159','2','08/06/2019','0','19','217','20','40'), ('161','2','08/03/2019','0','20','218','60','80'), ('163','2','08/15/2019','0','21','NULL','90','110')
create table #product (id int, vendor_id varchar(50) )
insert into #product values ('1','201'), ('1','201'), ('1','201'), ('2','203'), ('2','203'), ('2','203'), ('7','NULL'), ('8','207'), ('8','207'), ('8','207'), ('11','210'), ('11','210'), ('11','210'), ('11','210'), ('15','211'), ('15','211'), ('15','211'), ('16','206'), ('18','216'), ('19','NULL'), ('20','218'), ('21','219')
create table #vendor (vendor_id varchar(50), vendorname varchar(50) )
insert into #vendor values ('201','cola'), ('203','foam'), ('207','fill'), ('210','falon'), ('211','chiran'), ('216','Hummer'), ('218','sulps'), ('219','culp'), ('217','jko'), ('206','JINCO')
create table #asin (disable int, item_id int, lastconfirmed datetime2, lastconfirmedasin varchar(50) )
insert into #asin values ('0','123','12/19/18 10:19 PM','iopyu'), ('1','123','12/19/16 10:19 PM','hjyug'), ('1','125','5/19/19 10:19 PM','uirty'), ('0','125','12/19/16 10:19 PM','1yuio'), ('0','127','2/19/19 10:19 PM','klbnm'), ('1','127','12/19/18 10:19 PM','lopgh'), ('0','127','12/19/16 10:19 PM','nmbh'), ('0','129','11/19/16 10:19 PM','jklh'), ('0','131','11/19/19 10:19 PM','werat'), ('1','133','6/19/19 10:19 PM','vbnwe'), ('0','133','1/19/19 10:19 PM','mnwer'), ('0','133','11/19/17 10:19 PM','sdert'), ('0','135','6/19/19 10:19 PM','vbsdx'), ('0','137','6/19/17 10:19 PM','bnxct')
create table #pageviews (startdate date, lastconfirmedasin varchar(50), noofpageviews int, datetype varchar(20))
insert into #pageviews values ('05/08/2017','hjyug','102','day'), ('09/05/2017','hjyug','201','day'), ('10/05/2019','hjyug','1002','day'), ('09/05/2018','iopyu','345','month'), ('10/06/2018','iopyu','545','month'), ('06/05/2019','1yuio','300','day'), ('06/06/2019','1yuio','200','day'), ('09/04/2019','uirty','150','day'), ('11/4/2019','uirty','200','day'), ('12/4/2019','nmbh','300','day'), ('04/15/2019','lopgh','400','day'), ('04/15/2019','klbnm','500','day'), ('04/16/2019','klbnm','1000','day'), ('04/15/2019','jklh','600','day'), ('04/15/2019','werat','700','day'), ('04/15/2019','sdert','800','day'), ('04/15/2019','mnwer','900','day'), ('04/15/2019','vbnwe','1000','day'), ('04/15/2019','vbsdx','1100','day'), ('04/25/2019','vbsdx','3000','day'), ('04/15/2019','bnxct','1200','month'), ('05/31/2019','bnxct','2200','month'), ('04/16/2019','bnxct','90','day'), ('04/17/2019','bnxct','100','day'), ('04/18/2019','bnxct','120','day')
create table #promo (itemid int, promo_cost float, pre_promo_cost float, promo_start varchar(50), promo_end varchar(50))
insert into #promo values ('123','214.7','220','10/08/2019','22/08/2019'), ('123','225','230','01/02/2018','15/02/2018'), ('125','400','430','12/08/2019','19/08/2019'), ('125','380','390','15/02/2019','30/02/2019'), ('127','120','140','15/03/2019','30/03/2019'), ('129','80','100','15/04/2019','30/04/2019'), ('129','110','120','01/04/2019','10/04/2019'), ('131','80','100','01/02/2019','15/02/2019'), ('131','110','120','10/01/2019','15/01/2019'), ('133','230','420','10/01/2019','15/01/2019'), ('135','250','440','10/01/2019','15/01/2019'), ('137','270','460','10/01/2019','15/01/2019'), ('139','290','480','10/01/2019','15/01/2019'), ('141','310','500','10/01/2019','15/01/2019'), ('143','330','520','10/01/2019','15/01/2019'), ('145','350','540','10/08/2019','22/08/2019')
create table #output (itemid int, available int, expected date, isnotsellable int, pid int, vendor_id varchar(50), vendorname varchar(50), lastconfirmedasin varchar(50), pageviewsperday int, promoavg float, cost float, price float, [power] int, [priority] int)
insert into #output values ('151','1',NULL,'0','15','211','chiran','','0','0','30','50','0','1'), ('127','1',NULL,'0','1','201','cola','klbnm','750','6.3','60','80','60000','1'), ('125','2','08/05/2019','0','1','201','cola','','0','0','430','60','0','2'), ('143','1',NULL,'0','11','210','falon','','0','0','60','80','0','1'), ('145','1',NULL,'0','11','210','falon','','0','0','540','90','0','2'), ('149','1',NULL,'0','11','210','falon','','0','0','20','40','0','3'), ('137','1',NULL,'0','8','207','fill','bnxct','103','65.73','120','140','14420','1'), ('139','1',NULL,'0','8','207','fill','','0','0','30','50','0','2'), ('141','1',NULL,'0','8','207','fill','','0','0','40','60','0','3'), ('131','1',NULL,'0','2','203','foam','werat','700','30.16','70','90','63000','1'), ('135','1',NULL,'0','7','206','JINCO','vbsdx','2050','76','110','130','266500','1'), ('153','1',NULL,'0','16','206','JINCO','','0','0','40','60','0','2'), ('161','2','08/03/2019','0','20','218','sulps','','0','0','60','80','0','1')

现有尝试代码 #

; WITH firstsel AS (
 SELECT itemid, available, expected, isnotsellable, pid, vendor_id
 FROM #productdetail
 WHERE available = 1 AND isnotsellable = 0
 UNION ALL
 SELECT itemid, available, expected, isnotsellable, pid, vendor_id
 FROM #productdetail
 WHERE available = 2 AND isnotsellable = 0 AND expected <= '20190805'
 ),
 addvendorid AS (
 SELECT fs.itemid, fs.available, fs.expected, fs.pid, fs.isnotsellable,
 coalesce(fs.vendor_id, p.vendor_id) AS vendor_id
 FROM firstsel fs
 JOIN #product p ON fs.pid = p.id
 ),
 asinnumbered AS (
 SELECT item_id, lastconfirmedasin,
 row_number() OVER (PARTITION BY item_id ORDER BY lastconfirmedasin DESC) AS rowno
 FROM #asin
 )
 SELECT av.itemid, av.available, av.expected, av.isnotsellable, av.pid,
 av.vendor_id, v.vendorname, an.lastconfirmedasin,
 isnull(pv.pageviews, 0) AS pageviews,
 isnull(p.promoavg, 0) AS promoavg
 FROM addvendorid av
 JOIN #vendor v ON av.vendor_id = v.vendor_id
 LEFT JOIN (asinnumbered an
 JOIN (SELECT lastconfirmedasin, SUM(pageviews) AS pageviews
 FROM #pageviews GROUP BY lastconfirmedasin) AS pv
 ON an.lastconfirmedasin = pv.lastconfirmedasin)
 ON an.item_id = av.itemid AND an.rowno = 1
 LEFT JOIN (SELECT itemid, avg((pre_promo_price-promo_price)/promo_price*100) AS promoavg
 FROM #promo GROUP BY itemid) AS p
 ON p.itemid = av.itemid
 ORDER BY av.itemid

需求说明 #

需要完善上述SQL查询，使其满足所有业务规则，最终输出结果需匹配#output表的结构及数据逻辑。

内容的提问来源于stack exchange，提问作者user3369545