统计商品各项数据sql&统计历史数据sql

1.统计商品各项数据

需求就是将统计商品的 成交总数、订单数、商品价格、访问数量等。写了个sql记录一下，以便以后遇到类似的需求方便查看。

SELECT
	 productName,orderIdCount,actualPrice,accountCount,askNum
FROM
	(
SELECT
	count( order_id ) orderIdCount,
	product_id,
	p.NAME productName
FROM
	(
SELECT
	d.order_id,
	d.product_id,
	o.create_time,
	o.actual_price 
FROM
	yun_job_shop_order_details d
	LEFT JOIN yun_job_shop_order o ON d.order_id = o.order_id 
WHERE
	product_id IN ( 180, 170 ) 
	AND date( d.create_time ) IN ( '2022-10-26', '2022-10-25' ) 
	) b
	LEFT JOIN yun_job_shop_product p ON p.id = product_id 
GROUP BY
	product_id 
	) a left join (
	SELECT
product_id,
sum(actual_price) actualPrice
FROM
	(
SELECT
	d.order_id,
	d.product_id,
	o.create_time,
	o.actual_price 
FROM
	yun_job_shop_order_details d
	LEFT JOIN yun_job_shop_order o ON d.order_id = o.order_id 
WHERE
	product_id IN ( 180,170 ) 
	AND date( d.create_time ) IN ( '2022-10-26', '2022-10-25' ) 
	) b
	left join yun_job_shop_product p on
	p.id=product_id
	GROUP BY product_id) b on a.product_id=b.product_id
	
	left join (	SELECT
	product_id,
	count(b.account) accountCount
	FROM
		(
	SELECT
		d.order_id,
		d.product_id,
		o.create_time,
		o.account 
	FROM
		yun_job_shop_order_details d
		LEFT JOIN yun_job_shop_order o ON d.order_id = o.order_id 
	WHERE
		product_id IN ( 180,170 ) 
		AND date( d.create_time ) IN ( '2022-10-26', '2022-10-25' ) 
		GROUP BY 	o.account 
		) b
		left join yun_job_shop_product p on
		p.id=product_id
		GROUP BY product_id)c on c.product_id=a.product_id
		left join (SELECT
            count(b.account) askNum,
            product_id
				
            FROM
            (
            S