从同一SQL表中的不同值中查找百分比差异？

Question

我有一张表，用来跟踪几个月来某个特定地点的总价值。

期望的结果:我想比较今年一个月的价值和去年的价值。然后我想检查一下是否增加了一个百分比。

2014年(1月)=140-2013(1月)= 150 * 100 =- 6.67

表名- donation_tracker

​

提前谢谢你。

Answer 1

比方说，你需要逐个月地比较即时完成的12个月和之前的12个月。我猜你的桌子和列的名字，因为，我不知道他们。

让我们从头做起。

这是一个查询，它将发现最近的12个月的捐款逐个月。

SELECT YEAR(donation_date) AS donation_year, 
       MONTH(donation_date) AS donation_month,
       SUM(donation_amount) AS donation_amount
  FROM donations
 WHERE donation_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 13 MONTH
   AND donation_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
 GROUP BY YEAR(donation_date), MONTH(donation_date) 

这给出了这样的12行结果集(当NOW()恰好在2014年11月中旬)：

2013  11    145
2013  12    220
2014   1    123
2014   2     11
  ...
2014  10     45

诀窍是选择正确的donation_date值范围。

所以，现在你需要两个这样的结果集，一个主要是2014年，另一个是2013年。大多数情况下，2013年的情况看起来非常相似。你只需像这样再多活一年。

SELECT YEAR(donation_date) AS donation_year, 
       MONTH(donation_date) AS donation_month,
       SUM(donation_amount) AS donation_amount
  FROM donations
 WHERE donation_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 25 MONTH
   AND donation_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 13 MONTH
 GROUP BY YEAR(donation_date), MONTH(donation_date) 

这将是一个臭名昭著的俱乐部三明治查询，由这两个基本的查询。您可以像这样按月加入它们，然后在SELECT子句中进行百分比计算。

SELECT a.donation_month, 
       a.donation_amount AS this_year, 
       b.donation_amount AS last_year,
       100.0 * (a.donation_amount - b.donation_amount) / b.donation_amount as pct_increase
  FROM (
          /* this year's query */
       ) AS a 
  JOIN (
          /* last year's query */
       ) AS b ON a.donation_month = b.donation_month 
  ORDER BY a.donation_year, a.donation_month

这是整个俱乐部的三明治供你的服务生嚼。太棒了！

SELECT a.donation_month, 
       a.donation_amount AS this_year, 
       b.donation_amount AS last_year,
       100.0 * (a.donation_amount - b.donation_amount) / b.donation_amount as pct_increase
  FROM (
        SELECT YEAR(donation_date) AS donation_year, 
               MONTH(donation_date) AS donation_month,
               SUM(donation_amount) AS donation_amount
          FROM donations
         WHERE donation_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 13 MONTH
           AND donation_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL  1 MONTH
         GROUP BY YEAR(donation_date), MONTH(donation_date) 
       ) AS a 
  JOIN (
        SELECT YEAR(donation_date) AS donation_year, 
               MONTH(donation_date) AS donation_month,
               SUM(donation_amount) AS donation_amount
          FROM donations
         WHERE donation_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 25 MONTH
           AND donation_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 13 MONTH
         GROUP BY YEAR(donation_date), MONTH(donation_date) 
       ) AS b ON a.donation_month = b.donation_month 
  ORDER BY a.donation_year, a.donation_month

一旦你把整个俱乐部的三明治堆起来，看起来就很复杂了。但它实际上是一堆简单的子查询。

Answer 2

据我所知，对于特定位置的，您希望从去年到今年同一月份获得增长的百分比。使用查询。

SELECT D1.month, ROUND((D2.Donation_amount- D1.Donation_amount) * 100 / 
       D1.Donation_amount, 2)
FROM       donation_tracker D1
INNER JOIN donation_tracker D2 
ON d1.month = D2.month AND D1.year = D2.year - 1 
                       AND D1.Location_ID = D2.Location_ID;

Answer 3

这应该给你一个想法:)

样本数据：

CREATE TABLE t
    (`month` varchar(3), `year` int, `amount` int)
;

INSERT INTO t
    (`month`, `year`, `amount`)
VALUES
    ('jan', 2013, 150),
    ('feb', 2013, 180),
    ('jan', 2014, 140),
    ('feb', 2014, 160)
;

查询：

select
t1.month, round((t2.amount - t1.amount) * 100 / t1.amount, 2)
from
t t1
inner join t t2 on t1.month = t2.month and t1.year < t2.year;

结果：

| MONTH | ROUND((T2.AMOUNT - T1.AMOUNT) * 100 / T1.AMOUNT, 2) |
|-------|-----------------------------------------------------|
|   jan |                                               -6.67 |
|   feb |                                              -11.11 |