在Postgresql中按最大日期查詢值

我已經在這里問過這個問題，但關于我的問題的資訊較少。因此，我創建了一個包含更多資訊的新問題。

這是我擁有的示例表。每行包含用戶每次填充的資料。這樣整個表中的時間戳列不會為空。item下可能有未記錄的值，如果用戶沒有填寫。該ID是為每個記錄自動生成的列。

CREATE TABLE tbl (id int, customer_id text, item text, value text, timestamp timestamp);    
INSERT INTO tbl VALUES
(1, '001', 'price', '1000', '2021-11-01 01:00:00'),
(2, '001', 'price', '1500', '2021-11-02 01:00:00'),
(3, '001', 'price', '1400', '2021-11-03 01:00:00'),
(4, '001', 'condition', 'good', '2021-11-01 01:00:00'),
(5, '001', 'condition', 'good', '2021-11-02 01:00:00'),
(6, '001', 'condition', 'ok', '2021-11-03 01:00:00'),
(7, '001', 'feeling', 'sad', '2021-11-01 01:00:00'),
(8, '001', 'feeling', 'angry', '2021-11-02 01:00:00'),
(9, '001', 'feeling', 'fine', '2021-11-03 01:00:00'),
(10, '002', 'price', '1200', '2021-11-01 01:00:00'),
(11, '002', 'price', '1600', '2021-11-02 01:00:00'),
(12, '002', 'price', '2000', '2021-11-03 01:00:00'),
(13, '002', 'weather', 'sunny', '2021-11-01 01:00:00'),
(14, '002', 'weather', 'rain', '2021-11-02 01:00:00'),
(15, '002', 'price', '1900', '2021-11-04 01:00:00'),
(16, '002', 'feeling', 'sad', '2021-11-01 01:00:00'),
(17, '002', 'feeling', 'angry', '2021-11-02 01:00:00'),
(18, '002', 'feeling', 'fine', '2021-11-03 01:00:00'),
(19, '003', 'price', '1000', '2021-11-01 01:00:00'),
(20, '003', 'price', '1500', '2021-11-02 01:00:00'),
(21, '003', 'price', '2000', '2021-11-03 01:00:00'),
(22, '003', 'condition', 'ok', '2021-11-01 01:00:00'),
(23, '003', 'weather', 'rain', '2021-11-02 01:00:00'),
(24, '003', 'condition', 'bad', '2021-11-03 01:00:00'),
(25, '003', 'feeling', 'fine', '2021-11-01 01:00:00'),
(26, '003', 'weather', 'sunny', '2021-11-03 01:00:00'),
(27, '003', 'feeling', 'sad', '2021-11-03 01:00:00')
;

為了看清楚，我按id和timestamp對上表進行了排序。沒關系。

• 我們使用的是 Postgresql 版本：PostgreSQL 9.5.19
• 實際表包含超過 400 萬行
• 該專案列包含超過500個不同的專案，但不要擔心。我將最多使用 10 個專案進行查詢。在上表中，我只使用了 4 個專案。
• 我們還有另一個名為Customer_table 的表，其中包含一個唯一的 Customer_id，其中包含客戶的一般資訊。

從上表中，我想查詢資料以創建一個包含最新日期更新資料的表，如下所示。我將最多使用 10 個專案進行查詢，以便可能有 10 列。

customer_id  price  condition  feeling   weather .......(there may be other columns from item column)
   002        1900    null      fine      rain
   001        1400     ok       fine      null
   003        2000    bad       sad       sunny

這是我從以前的問題中得到的查詢，但我只詢問了兩個專案。

SELECT customer_id, p.value AS price, c.value AS condition
FROM  (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'condition'
   ORDER  BY customer_id, timestamp DESC
   ) c
FULL JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'price'
   ORDER  BY customer_id, timestamp DESC
   ) p USING (customer_id)

所以，如果有更好的解決方案，請幫助我。謝謝你。

uj5u.com熱心網友回復：

您可以嘗試使用其他方法row_number生成一個值，以根據最新資料過濾您的資料。然后，您可以根據所需的行號rn=1（我們將按降序排序）和專案名稱過濾您的記錄的 case 運算式的最大值聚合客戶 ID 。

這些方法不那么冗長，并且基于在線結果似乎更高效。在評論中告訴我如何在您的環境中復制它。

您可以EXPLAIN ANALYZE將這種方法與當前方法進行比較。在線環境中的結果提供：

當前方法

| Planning time: 0.129 ms                                                                                                      
| Execution time: 0.056 ms      

建議的方法 1

| Planning time: 0.061 ms                                                                                                 
| Execution time: 0.070 ms   

建議的方法 2

| Planning time: 0.047 ms                                                                                                 
| Execution time: 0.056 ms 

注意。您可以EXPLAIN ANALYZE在您的環境中比較這些我們無法在線復制的方法。每次運行的結果也可能不同。item還建議在列上使用索引和早期過濾器以提高性能。

架構 (PostgreSQL v9.5)

建議的方法 1

SELECT
    t1.customer_id,
    MAX(CASE WHEN t1.item='condition' THEN t1.value END) as conditio,
    MAX(CASE WHEN t1.item='price' THEN t1.value END) as price,
    MAX(CASE WHEN t1.item='feeling' THEN t1.value END) as feeling,
    MAX(CASE WHEN t1.item='weather' THEN t1.value END) as weather
FROM (
    SELECT
        * ,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id,item
            ORDER BY tbl.timestamp DESC
        ) as rn
    FROM
        tbl 
    -- ensure that you filter based on your desired items
    -- indexes on item column are recommended to improve performance
) t1
WHERE rn=1
GROUP BY
   1;

建議的方法 2

SELECT
    t1.customer_id,
    MAX(t1.value) FILTER (WHERE  t1.item='condition')  as conditio,
    MAX(t1.value) FILTER (WHERE  t1.item='price')  as price,
    MAX(t1.value) FILTER (WHERE  t1.item='feeling')  as feeling,
    MAX(t1.value) FILTER (WHERE  t1.item='weather')  as weather
    
FROM (
    SELECT
        * ,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id,item
            ORDER BY tbl.timestamp DESC
        ) as rn
    FROM
        tbl 
    -- ensure that you filter based on your desired items
    -- indexes on item column are recommended to improve performance
) t1
WHERE rn=1
GROUP BY
   1;

當前使用 EXPLAIN ANALYZE 的方法

EXPLAIN(ANALYZE,BUFFERS)
SELECT customer_id, p.value AS price, c.value AS condition
FROM  (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'condition'
   ORDER  BY customer_id, timestamp DESC
   ) c
FULL JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'price'
   ORDER  BY customer_id, timestamp DESC
   ) p USING (customer_id);

Suggested Approach 1 with EXPLAIN ANALYZE

EXPLAIN(ANALYZE,BUFFERS)
SELECT
    t1.customer_id,
    MAX(CASE WHEN t1.item='price' THEN t1.value END) as price,
    MAX(CASE WHEN t1.item='condition' THEN t1.value END) as conditio
    
FROM (
    SELECT
        * ,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id,item
            ORDER BY tbl.timestamp DESC
        ) as rn
    FROM
        tbl 
    where item IN ('price','condition')
) t1
WHERE rn=1
GROUP BY
   1;

Suggested Approach 2 with EXPLAIN ANALYZE

EXPLAIN(ANALYZE,BUFFERS)
SELECT
    t1.customer_id,
    MAX(t1.value) FILTER (WHERE  t1.item='price')  as price,
    MAX(t1.value) FILTER (WHERE  t1.item='condition')  as conditio
    
FROM (
    SELECT
        * ,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id,item
            ORDER BY tbl.timestamp DESC
        ) as rn
    FROM
        tbl 
    where item IN ('price','condition')
) t1
WHERE rn=1
GROUP BY
   1;

查看 DB Fiddle 上的作業演示

uj5u.com熱心網友回復：

你在一張大桌子上操作。你提到了 400 萬行，顯然在增長。在查詢...

• 所有客戶
• 所有專案
• 有幾排每(customer_id, item)
• 與窄行（小行大小）

... ggordon的解決方案有row_number()是巨大的。而且也很短。
在整個表有一個要處理的順序掃描。不會使用指數。
但更喜歡使用現代聚合語法的“方法 2 ” FILTER。它更清晰、更快。在此處查看性能測驗：

• 對于絕對性能，SUM 更快還是 COUNT？

方法 3：以 crosstab()

crosstab()通常更快，尤其是對于多個專案。看：

• PostgreSQL 交叉表查詢

SELECT *
FROM   crosstab(
   $$
   SELECT customer_id, item, value
   FROM  (
      SELECT customer_id, item, value
           , row_number() OVER (PARTITION BY customer_id, item ORDER BY t.timestamp DESC) AS rn
      FROM   tbl t
      WHERE  item = ANY ('{condition,price,feeling,weather}')  -- your items here ...
      ) t1
   WHERE  rn = 1
   ORDER  BY customer_id, item
   $$
 , $$SELECT unnest('{condition,price,feeling,weather}'::text[])$$  -- ... here ...
   ) AS ct (customer_id text, condition text, price text, feeling text, weather text);  -- ... and here ...

方法 4：LATERAL子查詢

如果頂部列出的一個或多個條件不適用，則上述查詢的性能會迅速下降。

首先，最多只涉及“500 個不同專案”中的 10 個。這是大表的最大 ~ 2%。相比之下，僅此一項就應該使以下查詢之一（快得多）：

SELECT *
FROM  (SELECT customer_id FROM customer) c
LEFT   JOIN LATERAL (
   SELECT value AS condition
   FROM   tbl t
   WHERE  t.customer_id = c.customer_id
   AND    t.item = 'condition'
   ORDER  BY t.timestamp DESC
   LIMIT  1
   ) AS t1 ON true
LEFT   JOIN LATERAL (
   SELECT value AS price
   FROM   tbl t
   WHERE  t.customer_id = c.customer_id
   AND    t.item = 'price'
   ORDER  BY t.timestamp DESC
   LIMIT  1
   ) AS t2 ON true
LEFT   JOIN LATERAL (
   SELECT value AS feeling
   FROM   tbl t
   WHERE  t.customer_id = c.customer_id
   AND    t.item = 'feeling'
   ORDER  BY t.timestamp DESC
   LIMIT  1
   ) AS t3 ON true
--  ... more?

關于LEFT JOIN LATERAL：

• LATERAL JOIN 和 PostgreSQL 中的子查詢有什么區別？

關鍵是獲得一個索引（僅）掃描相對較少的查詢計劃，以取代大表上昂貴的順序掃描。
需要一個適用的index，顯然：

CREATE INDEX ON tbl (customer_id, item);

或者更好（在 Postgres 9.5 中）：

CREATE INDEX ON tbl (customer_id, item, timestamp DESC, value);

在 Postgres 11 或更高版本中，這會更好，但是：

CREATE INDEX ON tbl (customer_id, item, timestamp DESC) INCLUDE (value);

請參閱此處或此處或此處。

如果只有少數專案感興趣，這些專案的部分索引會更好。

方法 5：相關子查詢

SELECT c.customer_id
     , (SELECT value FROM tbl t WHERE t.customer_id = c.customer_id AND t.item = 'condition' ORDER BY t.timestamp DESC LIMIT 1) AS condition
     , (SELECT value FROM tbl t WHERE t.customer_id = c.customer_id AND t.item = 'price'     ORDER BY t.timestamp DESC LIMIT 1) AS price
     , (SELECT value FROM tbl t WHERE t.customer_id = c.customer_id AND t.item = 'feeling'   ORDER BY t.timestamp DESC LIMIT 1) AS feeling
     , (SELECT value FROM tbl t WHERE t.customer_id = c.customer_id AND t.item = 'weather'   ORDER BY t.timestamp DESC LIMIT 1) AS weather
FROM   customer c;

不像 那樣通用LATERAL，但足以達到目的。指標要求與方法 4 相同。

在大多數情況下，方法 5 將是性能之王。

db<>在這里擺弄

改進您的關系設計和/或升級到當前版本的 Postgres 也會大有幫助。

標籤：sql PostgreSQL的 每组最大 n

上一篇：根據名稱搜索資料庫并回傳多個。姜戈

下一篇：執行st_union時如何解決GEOS錯誤？