分組找出最早的一筆資料

sql database 資料庫

keelung0829 2014-02-09 04:26:42 ‧ 16816 瀏覽

我想從表中找出每天內每一個消費者所購買的最高價錢的物品這一資料。
若是所購買最高價錢的物品有多個，取最早買的那一物品。
欄位與查詢與法如下：
schema：customer,product,price,logtime
buylist這張表約有50萬筆資料
index為(logtime,customer)
在沒有cte與row_number的情況下，
想請問各位前輩有什麼方法可以優化下面的SQL?
謝謝!

select e.customer ,e.product ,e.price ,e.logtime
from 
	(
	select b.customer ,min(b.logtime) as logtime
	from
		(select customer ,max(price) as price
		from buylist
		where logtime >= '2014-02-08' and logtime &lt; '2014-02-09'
		group by customer)a 
	join 
		(select customer ,product ,price ,logtime 
		from buylist 
		where logtime >= '2014-02-08' and logtime &lt; '2014-02-09')b
	 on a.customer = b.customer and a.price = b.price
	group by b.customer
	)d 
join
	(
	select customer ,product ,price ,logtime 
	from buylist 
	where logtime >= '2014-02-08' and logtime &lt; '2014-02-09'
	)e 
 on d.customer = e.customer and d.logtime = e.logtime;

外獅佬 iT邦大師 1 級 ‧ 2014-02-09 23:41:57 檢舉

奇怪...這不是下幾個order就可以解決了嗎？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2014-02-12 15:29:31

最佳解答

&lt;pre class="c" name="code">-- Using Postgresql

CREATE TABLE akina1.customer (
id SERIAL NOT NULL PRIMARY KEY,
name CHAR(10) NOT NULL);

INSERT INTO customer(name) VALUES
('賽大'), ('總裁'), ('老鷹');

CREATE TABLE akina1.sales (
id SERIAL NOT NULL PRIMARY KEY,
cid INT NOT NULL REFERENCES akina1.customer(id),
product CHAR(30) NOT NULL,
price INT NOT NULL,
logtime TIMESTAMP NOT NULL,
usefor CHAR(40) NOT NULL
);


INSERT INTO akina1.sales (cid, product, price, logtime, usefor) VALUES
(1, 'R3D3', 2000, '2014-02-08 13:24:57', '修理太空船'),
(2, '瑪殺拉弟', 200000, '2014-02-08 15:24:28', '送MM'),
(1, 'C3PM', 3000, '2014-02-09 09:14:30', '閒天打屁用'),
(2, '灣流G650', 999000, '2014-02-09 09:30:10', '剛那台G650的顏色,老婆不喜歡'),
(2, '灣流G550', 800000, '2014-02-09 07:00:44', '買飛機剛剛好而已'),
(2, '灣流G650', 999000, '2014-02-09 08:24:33', 'G550不夠好'),
(3, 'PHP秘笈', 300, '2014-02-09 06:30:34', '學習PHP'),
(3, '降虎17掌實戰手冊', 500, '2014-02-09 06:32:32', '今晚打老虎'),
(3, '快快樂樂學把妹', 500, '2014-02-09 06:25:52', '把妹大計由此開始');

--測試資料依照時間順序列出
SELECT *
  FROM akina1.sales
 ORDER BY logtime;

 id | cid | product         | price  |       logtime       |            usefor                        
----+-----+---------------------------------------+--------+---------------------
  1 |   1 | R3D3            |   2000 | 2014-02-08 13:24:57 | 修理太空船                                   
  2 |   2 | 瑪殺拉弟        | 200000 | 2014-02-08 15:24:28 | 送MM                                     
  9 |   3 | 快快樂樂學把妹  |    500 | 2014-02-09 06:25:52 | 把妹大計由此開始                                
  7 |   3 | PHP秘笈         |    300 | 2014-02-09 06:30:34 | 學習PHP                                   
  8 |   3 | 降虎17掌實戰手冊 |    500 | 2014-02-09 06:32:32 | 今晚打老虎                                   
  5 |   2 | 灣流G550        | 800000 | 2014-02-09 07:00:44 | 買飛機剛剛好而已                                
  6 |   2 | 灣流G650        | 999000 | 2014-02-09 08:24:33 | G550不夠好                                 
  3 |   1 | C3PM            |   3000 | 2014-02-09 09:14:30 | 閒天打屁用                                   
  4 |   2 | 灣流G650        | 999000 | 2014-02-09 09:30:10 | 剛那台G650的顏色,老婆不喜歡                        
(9 筆資料列)

-- 假設抓 2014-02-09 那天,每個人最高消費的產品,當有價格相同的,抓最早的.
SELECT c.name
     , s.product
     , s.price
     , s.usefor
     , s.logtime
 FROM (SELECT cid
            , product
            , price
            , logtime
            , usefor
            , rank() OVER (PARTITION BY cid ORDER BY price DESC, logtime) AS srank
         FROM akina1.sales
        WHERE logtime >= '2014-02-09 00:00:00'::timestamp 
          AND logtime &lt;= '2014-02-09 23:59:59'::timestamp) s
    , akina1.customer c
WHERE s.srank = 1
  AND s.cid = c.id;

 name     | product        | price  |  usefor       |       logtime       
----------+-----------------------------------------+---------------------
 賽大     | C3PM           |   3000 | 閒天打屁用     | 2014-02-09 09:14:30
 總裁     | 灣流G650       | 999000 | G550不夠好     | 2014-02-09 08:24:33
 老鷹     | 快快樂樂學把妹 |    500 | 把妹大計由此開始| 2014-02-09 06:25:52
(3 筆資料列)

回應
分享
檢舉

登入發表回應

richardsuma

iT邦大師 1 級 ‧ 2014-02-09 21:38:41

index 改為 logtime,customer,price

select customer,max(price),product
from buylist
where CONVERT(varchar(12),logtime,112)='20140208'
group by customer
having min(logtime)

回應 7
分享
檢舉

看更多先前的回應...收起先前的回應...

mochidawn iT邦新手 5 級 ‧ 2014-02-10 15:15:15 檢舉

having不是這樣用的吧

keelung0829 iT邦新手 4 級 ‧ 2014-02-10 17:28:11 檢舉

hi,richardsuma！
抱歉了，今天太忙，現在才能好好回應
在having中有彙總函式的寫法，我也是第一次接觸到。
只是剛好我的DB不是mysql，而是一隻笨重的大象(版本過舊了)
但還是很謝謝你提供另一種想法！
謝謝！

richardsuma iT邦大師 1 級 ‧ 2014-02-11 22:00:47 檢舉

再試試看下列SQL statement:
(百分之96% 會花在 index 查詢，測試: SQL2012 ，data 73.6萬筆，時間不到0.5秒)

SELECT a.logtime, a.customer, a.product, a.price
FROM buylist a, (select c.logtime, c.customer, c.max(price) as price
from buylist c
where CONVERT(varchar(12), c.logtime,112)='20140208'
group by CONVERT(varchar(12), c.logtime,112), c.customer)
as b
WHERE a.logtime=b.logtime
and a.customer=b.customer
and a.price=b.price

keelung0829 iT邦新手 4 級 ‧ 2014-02-12 14:09:41 檢舉

hi,richardsuma!
這語法跑起來怪怪的，在子查詢B所找出的結果不是正確的。

richardsuma iT邦大師 1 級 ‧ 2014-02-12 23:43:52 檢舉

可能少了 having min(logtime)

試試:
SELECT a.logtime, a.customer, a.product, a.price
FROM buylist a,
(select c.logtime, c.customer, c.max(price) as price
from buylist c
where CONVERT(varchar(12), c.logtime,112)='20140208'
group by CONVERT(varchar(12), c.logtime,112), c.customer
having min(logtime)) as b
WHERE a.logtime=b.logtime
and a.customer=b.customer
and a.price=b.price

richardsuma iT邦大師 1 級 ‧ 2014-02-13 11:39:04 檢舉

修正如下:

SELECT a.logtime, a.customer, a.product, a.price
FROM buylist a,
(
select c.logtime, c.customer, max(price) as price
from buylist c
where CONVERT(varchar(12), c.logtime,112)='20140208'
group by CONVERT(varchar(12), c.logtime,112), c.customer
having min(logtime)>='20140208'
) as b
WHERE a.logtime=b.logtime
and a.customer=b.customer
and a.price=b.price

keelung0829 iT邦新手 4 級 ‧ 2014-02-13 12:07:14 檢舉

hi,richardsuma!
這出來的結果還是怪怪的。
還是非常感謝你所提供的參考。

登入發表回應

sssss1101

iT邦新手 4 級 ‧ 2014-02-10 13:34:16

select customer,product,price,logtime from buylist
where price in (select MAX(price) from buylist group by logtime,customer )
and logtime in (selecr MIN(logtime) from buylist group by logtime,customer )

回應 3
分享
檢舉

keelung0829 iT邦新手 4 級 ‧ 2014-02-10 17:57:47 檢舉

hi,sssss1101！
這寫法試起來怪怪的！
select MAX(price) from buylist group by logtime,customer
或selecr MIN(logtime) from buylist group by logtime,customer
這樣每一筆資料永遠都是一個最大值或最小值
重複性太大了。

sssss1101 iT邦新手 4 級 ‧ 2014-02-11 11:02:30 檢舉

取最高價錢且最早買的產品，你再試試看!
select customer,product,price,logtime from buylist
where price in (select MAX(price) from buylist group by customer,product )
and logtime in (select MIN(logtime) from buylist group by customer,product )