题解 | 商品销售总额分布

#anta
# every pay methode number distribution desc
with t1 as
(select ul.*,pi.price,pi.product_name, case when ul.pay_method='' and ul.step='select' then 'error' else  ul.pay_method end as method_with_dirty
from user_client_log ul left join product_info pi on ul.product_id=pi.product_id
where pi.product_name="anta")


select t1.method_with_dirty,count(*) as number
from t1
group by t1.method_with_dirty
having t1.method_with_dirty!=""
order by number desc

本题两个注意事项：

1.有两种脏数据需要处理 1.题目明确指出的 “”且 ul.step = select的脏数据，此类脏数据才会标为error，计入结果

2.其余脏数据照样得剔除。