数据分析技巧：MySQL常用查询案例（持续更新中）

数据源：电商用户订单数据表。

表：sales

查询1：按月统计销售额

select extract(YEAR_MONTH from ORDERDATE) as period,sum(AMOUNTINFO) as amount
from sales
group by period

查询1：按月统计销售额

查询2：按月统计环比

select thisMonth.period as Period,
    thisMonth.amount as thisMonthAmount,lastmonth.amount as lastMonthAmount,
    (thisMonth.amount-lastmonth.amount)*100/lastmonth.amount as Rate 
from
    (select extract(YEAR_MONTH from ORDERDATE) as period,sum(AMOUNTINFO) as amount
    from sales
    group by period) as thisMonth
    left join
    (select extract(YEAR_MONTH from (DATE_ADD(ORDERDATE,INTERVAL 1 MONTH))) as period,sum(AMOUNTINFO) as amount
    from sales
    group by period) as lastMonth
    on thisMonth.period=lastMonth.period