在正常的業(yè)務(wù)邏輯中,對客戶的分析是必須的也是最經(jīng)常用到的,根據(jù)時間維度計算計算指標連續(xù)兩月環(huán)比情況也是一道必須面對的題目。

國慶放假 先寫代碼 后面再拆分解釋

SELECT a.*,b.年月 AS 上月,b.金額 AS 上月金額,(a.金額-b.金額) AS 金額較上月增長
FROM (SELECT a2.*,(@i:=@i+1) AS ID1FROM (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 a1WHERE a1.pay_money >0GROUP 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相等 把所有數(shù)據(jù)記錄下沉一行SELECT b2.*,(@j:=@j+1) AS ID2FROM (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 b1WHERE b1.pay_money >0GROUP 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#同一用戶相連兩月金額保持在一行

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/Mr-Cxy/p/5919212.html

總結(jié)

以上是生活随笔為你收集整理的MySQL计算指标连续两月金额相比_20160929的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。