作者 主題: Select 遞增資料中的 daily diff  (閱讀 4179 次)

0 會員 與 1 訪客 正在閱讀本文。

secret1029

  • 可愛的小學生
  • *
  • 文章數: 15
    • 檢視個人資料
Select 遞增資料中的 daily diff
« 於: 2011-07-20 16:13 »
id   date                 count           diff
18000   2011-07-11   2000      2000
18001   2011-07-12   5000      3000
18002   2011-07-13   6000      1000

原有Mysql 欄位是id, date,count  , 因為count是每天遞增的,現在想求得daily diff,請問sql高手有語法或函數可以達到此目的嗎? 感謝

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
Re: Select 遞增資料中的 daily diff
« 回覆 #1 於: 2011-07-20 17:10 »

CREATE TABLE test0720 (
id INT NOT NULL PRIMARY KEY,
tdate DATE NOT NULL,
count INT NOT NULL
);

INSERT INTO test0720 VALUES
(18000, '2011-07-11', 2000),
(18001, '2011-07-12', 5000),
(18002, '2011-07-13', 6000);


SELECT c.id, c.tdate, c.count, (c.count - 0) AS diff
FROM test0720 c, (SELECT min(id) minid FROM test0720) d
WHERE c.id = d.minid
UNION
SELECT b.id, b.tdate, b.count, b.count - a.count AS diff
FROM test0720 a, test0720 b
WHERE b.id = a.id+1;

+-------+------------+-------+------+
| id    | tdate      | count | diff |
+-------+------------+-------+------+
| 18000 | 2011-07-11 |  2000 | 2000 |
| 18001 | 2011-07-12 |  5000 | 3000 |
| 18002 | 2011-07-13 |  6000 | 1000 |
+-------+------------+-------+------+

« 上次編輯: 2011-07-20 17:26 由 bunko »

secret1029

  • 可愛的小學生
  • *
  • 文章數: 15
    • 檢視個人資料
Re: Select 遞增資料中的 daily diff
« 回覆 #2 於: 2011-07-20 22:04 »
太讚了..真是高手...一下子就解出來了

Yamaka

  • 俺是博士!
  • *****
  • 文章數: 4913
    • 檢視個人資料
    • http://www.ecmagic.com
Re: Select 遞增資料中的 daily diff
« 回覆 #3 於: 2011-07-21 00:45 »
必要條件: id 必須是連續且不跳號

可以簡化為這樣

代碼: [選擇]
SELECT a.*,(a.count - IFNULL(b.count,0)) AS diff
FROM test201 a
LEFT JOIN test201 b ON (b.id = a.id-1)
ORDER BY a.id ASC

Yamaka

  • 俺是博士!
  • *****
  • 文章數: 4913
    • 檢視個人資料
    • http://www.ecmagic.com
Re: Select 遞增資料中的 daily diff
« 回覆 #4 於: 2011-07-22 11:28 »
剛才吃早餐時突然想到這篇
想說之前貼的版本要滿足必要條件才行
所以就腦力激盪一下, 在 left join 做點修改

代碼: [選擇]
SELECT a.*,(a.count - IFNULL(b.count,0)) AS diff, b.id
FROM test201 a
LEFT JOIN test201 b ON (b.id IN(SELECT MAX(c.id) FROM test201 c WHERE c.id<a.id))
ORDER BY a.id ASC

這樣就算資料列中間有刪除資料(跳號)也能正確計算

代碼: [選擇]
18000 2011-07-11 2000 2000 NULL
18001 2011-07-12 5000 3000 18000
18002 2011-07-13 6000 1000 18001
18005 2011-07-16 7500 1500 18002
18015 2011-07-26 9000 1500 18005
18019 2011-07-30 11300 2300 18015


我用的 MySQL 版本還無法在 subquery 使用 limit
不過可以用 MAX 來做到類似的結果  :D

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
Re: Select 遞增資料中的 daily diff
« 回覆 #5 於: 2011-07-22 14:57 »
補充一下,也可以用自己產生編號的方式.
輸入跳號測試資料:
代碼: [選擇]
INSERT INTO test0720 VALUES
(18005, '2011-07-16', 7500),
(18015, '2011-07-26', 9000),
(18019, '2011-07-30', 11300);
另外的方式:
代碼: [選擇]
SELECT a.id AS id, a.tdate AS tdate, a.count AS 'count', coalesce(a.count - b.count, a.count) AS diff
FROM ( SELECT @inum := @inum+1 AS anum, i.id, i.tdate, i.count
        FROM (SELECT id, tdate, count
               FROM test0720 ORDER BY id) i,
              (SELECT @inum :=0) r
)a
LEFT JOIN  ( SELECT @jnum := @jnum+1 AS bnum, j.id, j.tdate, j.count
               FROM (SELECT id, tdate, count
                      FROM test0720 ORDER BY id) j,
              (SELECT @jnum :=0) s
)b
ON (b.bnum = a.anum - 1);


+-------+------------+-------+------+
| id    | tdate      | count | diff |
+-------+------------+-------+------+
| 18000 | 2011-07-11 |  2000 | 2000 |
| 18001 | 2011-07-12 |  5000 | 3000 |
| 18002 | 2011-07-13 |  6000 | 1000 |
| 18005 | 2011-07-16 |  7500 | 1500 |
| 18015 | 2011-07-26 |  9000 | 1500 |
| 18019 | 2011-07-30 | 11300 | 2300 |
+-------+------------+-------+------+



使用coalesce()主要是考慮到coalesce()是ANSI-SQL的函數,功能類似IFNULL()