让我们假设我有产品,并保存与它们相关的视图和销售。现在我想获得特定产品的每日浏览量和销售额。一个产品可能不会每天都有浏览量或销售额。
- 产品:product_id
- 视图:view_id,product_id,已创建
- 销售:sale_id,product_id,已创建
SELECT products.product_id,
COALESCE(views.sum,0) AS total_views,
COALESCE(sales.sum,0) AS total_sales,
views.day
FROM products
LEFT JOIN (
SELECT COUNT(*) AS sum, product_id, date(created) as day
FROM views
GROUP BY product_id, day
) AS views
ON views.product_id = products.product_id
LEFT JOIN (
SELECT COUNT(*) AS sum, product_id, date(created) as day
FROM sales
GROUP BY product_id, day
) AS sales
ON sales.product_id = products.product_id
WHERE products.product_id = 15041
GROUP BY views.day, sales.day
这每天返回多行,而不是一行,我认为问题是我不知道如何正确地将每天的JOIN连接在一起。
如何修复使此查询按预期工作?是否建议进一步优化?
输出(错误(:
product_id total_views total_sales day
15041 23 19 2019-11-03
15041 23 37 2019-11-03
15041 57 19 2019-11-04
15041 57 37 2019-11-04
15041 70 19 2019-11-05
15041 70 37 2019-11-05
正确的输出是:
product_id total_views total_sales day
15041 23 0 2019-11-03
15041 57 19 2019-11-04
15041 70 37 2019-11-05
用户"METAL"输出:
product_id total_views total_sales day
15041 1 0 2019-11-03
15041 1 1 2019-11-04
15041 1 1 2019-11-05
假设您的产品有浏览量或销售额,我建议:
select product_id, sum(views) as views, sum(sales) as sales
from ((select product_id, date(created) as day, count(*) as views, 0 as sales
from views
where product_id = 15041
group by product_id, date(created)
) union all
(select product_id, date(created) as day, 0 as views, count(*) as sales
from sales
where product_id = 15041
group by product_id, date(created)
)
) vs
group by product_id, day;
我怀疑left join
到products
表真的是必要的
尝试在sales
和views
表上使用UNION
而不是join
select p.product_id,
, coalesce(sum(case when t1.col = 'views' then ct else 0 end), 0) as total_views
, coalesce(sum(case when t1.col = 'sales' then ct else 0 end), 0) as total_sales
, t1.dt
from products p
left join
(select count(1) as ct, product_id, date(created) dt, 'sales' as col
from sales group by date(created), product_id
union
select count(1) as ct, product_id, date(created), 'views'
from views group by date(created), product_id) t1 on t1.product_id = p.product_id
group by p.product_id, t1.dt