• MySQL计算指标连续两月金额相比_20160929


    在正常的业务逻辑中,对客户的分析是必须的也是最经常用到的,根据时间维度计算计算指标连续两月环比情况也是一道必须面对的题目。

    国庆放假 先写代码 后面再拆分解释

    SELECT a.*,b.年月 AS 上月,b.金额 AS 上月金额,(a.金额-b.金额) AS 金额较上月增长
    FROM (
    
        SELECT a2.*,(@i:=@i+1) AS ID1
        FROM (
            SELECT a1.city AS 城市,username AS 用户ID,DATE_FORMAT(a1.order_date,"%Y%m") AS 年月,SUM(a1.pay_money) AS 金额,LAST_DAY(order_date) AS 月最后一天
            FROM `test_a03order` AS a1
            WHERE a1.pay_money >0
            GROUP BY a1.city,a1.username,DATE_FORMAT(a1.order_date,"%Y%m")
        ) AS a2,(SELECT @i:=0) AS it1 #ID1从1自增长
    ) AS a
    LEFT JOIN (#和上面代码一样 无非是ID一个从1 一个从2  通过ID相等 把所有数据记录下沉一行
        SELECT b2.*,(@j:=@j+1) AS ID2
            FROM (
                SELECT b1.city AS 城市,username AS 用户ID,DATE_FORMAT(b1.order_date,"%Y%m") AS 年月,SUM(b1.pay_money) AS 金额,LAST_DAY(order_date) AS 月最后一天
                FROM `test_a03order` AS b1
                WHERE b1.pay_money >0
                GROUP BY b1.city,b1.username,DATE_FORMAT(b1.order_date,"%Y%m")
            ) AS b2,(SELECT @j:=1) AS it2#ID2从2自增长
    ) AS b ON a.ID1=b.ID2 AND a.用户ID=b.用户ID AND PERIOD_DIFF(DATE_FORMAT(a.月最后一天,"%Y%m"),DATE_FORMAT(b.月最后一天,"%Y%m"))=1#同一用户相连两月金额保持在一行

     

  • 相关阅读:
    读写锁机制原理
    jvm
    (WPF) 再议binding:点击User Control时,User Control变换颜色或做其他的处理。
    (WF)
    (C# ) 解析XML。
    (C#) 调用执行批处理文件
    (WPF, Service) 删除注册表中的USB Enum值.
    (C#) 文件操作
    (C#) Parse xml 时, 返回的node值总是null。
    (PowerShell) Managing Windows Registry
  • 原文地址:https://www.cnblogs.com/Mr-Cxy/p/5919212.html
Copyright © 2020-2023  润新知