今天要实现接口13:平效接口
一开始想的是,建一个销量表a作为daily中间表,字段:id, bid, date, pack_num, area_id,库位面积表b。a的area_id和b的id作为连接,这样a的每一天就能对应到b的area:
sql : select avg(a.pack_num)/b.area as efficiency_per_square_meter, b.start_date, b.end_date from a join b on ( a.bid = {bid} and a.date between {startDate} and {endDate} and a.bid = b.bid and a.area_id = b.id) GROUP BY start_date
但有一个担忧,就是这样写的话 area表一定要先于销量表而存在,因为Python脚本每天定时执行插入销量表的时候,要从area表中查当天的areaId插入销量表。
所以改成了 销量表里不存area相关值,降低耦合度:
SELECT AVG(a.pack_num)/b.area AS efficiency_per_square_meter, b.start_date, b.end_date
FROM daily_total a
JOIN brand_area b ON (a.date BETWEEN 20220118 and 20220122 and a.bid = 4 and
a.bid = b.bid and a.date >= b.start_date and a.date <= b.end_date)
GROUP BY start_date
order by start_date asc