/*注意：聚合函数里的列必须出现在group by里,注意用户需不需要去重，计算总销售额并不需要分组！！！
联表：from sales_tb s
join goods_tb g on s.goods_id = g.goods_id
计数：每种商品销售额：sum(sales_num * goods_price) over(partition by goods_id) as price；  销售总额：sum(price) as sales_total;  
客户总数量：count(distinct user_id) as user_num
客单价：总销售额/用户数量，记得和价钱有关的都保留三位小数
排序：无
筛选：2021年12月下旬：where sales_date between "2021-12-21" and "2021-12-31"
*/
select t.sales_total
,round(t.sales_total / t.user_cnt,3) as per_trans
from
(select sum(s.sales_num * g.goods_price) as sales_total
,count(distinct user_id) as user_cnt
from sales_tb s
join goods_tb g on s.goods_id = g.goods_id
where sales_date between "2021-12-21" 
and "2021-12-31" 
) t