mysql+union all+环比增长率统计

需求:订单数据在不同的表,需要联合到一起做数据分析(union 之前不能排序不然会报错)

select b.company,count(*),sum(tmp_order.amount),
DATEDIFF(CURRENT_TIME,max(tmp_order.dispatch_date)) as '未接单天数' from
(
select amount,supplierid,dispatch_date from a_order where order_status NOT IN (- 1,-10, 0 )
union ALL
select amount,supplierid,dispatch_date from b_order where order_status not in(2,3)
)tmp_order
right join supplier b on tmp_order.supplierid=b.supplierid
where b.type=1
GROUP BY b.supplierid
order by sum(amount) desc

SELECT 
    CASE b.supplier_type 
        WHEN 1 THEN '供应商' 
        WHEN 2 THEN '销售' 
    END AS type,
    YEAR(a.addtime) AS order_year,
    MONTH(a.addtime) AS order_month,
    COUNT(*) AS order_numbers,
    SUM(a.order_amount) AS order_amounts 
FROM 
    jingdong_order a 
    INNER JOIN dw_supplier b ON a.supplier_id = b.supplier_id 
WHERE 
    YEAR(a.addtime) = '2023' 
    AND a.order_status = 1 
    AND a.dispatch_status IN (1,2)
GROUP BY 
    b.supplier_type,
    MONTH(a.addtime)
union all
SELECT 
    CASE b.supplier_type 
        WHEN 1 THEN '供应商' 
        WHEN 2 THEN '销售' 
    END AS type,
    YEAR(a.create_time) AS order_year,
		MONTH(a.create_time) AS order_month,
    COUNT(*) AS order_numbers,
    SUM(a.order_amount) AS order_amounts 
FROM 
    tianmao_order a 
    INNER JOIN dw_supplier b ON a.supplier_id = b.supplier_id 

季度的环比增长率

#季度的环比增长率
SELECT
	now_quarter_sale.now_year as '年',
	now_quarter_sale.a_quarter as '季度',
	now_quarter_sale.now_quarter as '年/季度',
	now_quarter_sale.amount as '订单金额',
CASE
	
	WHEN last_quarter_sale.old_amount IS NULL 
	OR last_quarter_sale.old_amount = 0 THEN
	0 ELSE CONVERT ( ( ( now_quarter_sale.amount - last_quarter_sale.old_amount ) / last_quarter_sale.old_amount ) / 100, DECIMAL ( 10, 4 ) ) 
	END  "比上一个季度增长率"
FROM
	(
		(
		SELECT
			date_format( a.addtime, '%Y' ) AS now_year,
			QUARTER ( a.addtime ) a_quarter,
			CONCAT( YEAR ( a.addtime ), QUARTER ( a.addtime ) ) now_quarter,
			sum( order_amount ) AS amount 
		FROM
			order a 
		GROUP BY
			CONCAT( YEAR ( a.addtime ), QUARTER ( a.addtime ) ),
			date_format( a.addtime, '%Y' ),
			QUARTER ( a.addtime ) 
		) now_quarter_sale
		LEFT JOIN (
		SELECT
			date_format( b.addtime, '%Y' ) AS now_year,
			CONCAT( YEAR ( b.addtime ), QUARTER ( DATE_ADD( b.addtime, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
			sum( order_amount ) AS old_amount 
		FROM
			order b 
		GROUP BY
			CONCAT( YEAR ( b.addtime ), QUARTER ( DATE_ADD( b.addtime, INTERVAL 1 QUARTER ) ) ) 
		) last_quarter_sale ON now_quarter_sale.now_quarter = last_quarter_sale.last_quarter 
)
order by now_quarter_sale.now_year desc, now_quarter_sale.a_quarter desc

月度的环比增长率

#按照月份环比增长率
SELECT
	now_sale.now_time as '月',
CASE
	
	WHEN old_amount IS NULL 
	OR old_amount = 0 THEN
	0 ELSE ( CONVERT ( ( ( amount - old_amount ) / old_amount ) / 100, DECIMAL ( 10, 4 ) ) ) 
	END "月环比增长率" 
FROM
	(
		(
		SELECT
			date_format( a.create_time, '%Y-%m' ) AS now_time,
			sum( order_amount ) AS amount 
		FROM
			order a 
		GROUP BY
			date_format( a.create_time, '%Y-%m' ) 
		) now_sale
		LEFT JOIN (
		SELECT
			date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) AS now_time,
			sum( order_amount ) AS old_amount 
		FROM
			order b 
		GROUP BY
			date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) 
		) old_sale ON now_sale.now_time = old_sale.now_time