從單個快照表中選擇多個“最新”行，每個故事一個快照

我有一張表，其中包含多個故事的分析資料的多個快照。資料連同它所采用的時間戳以及資料所指的 story_id 一起存盤。

id: integer auto_increment
story_id: string
timestamp: datetime
value: number

我需要value在 id 串列中為每個故事（即每個唯一的 storyId）提取最新的。

我寫了一個查詢，但它的規模是災難性的。

SELECT story_id, value
FROM table
WHERE story_id IN ('1','2','3')
AND id = (SELECT id
          FROM table inner
          WHERE inner.story_id = table.story_id
          ORDER BY timestamp DESC
          LIMIT 1)

進行此查詢的更有效方法是什么？

很高興知道：

• story_id 必須是一個字串，它來自外部資料源
• story_id 和 timestamp 已經有索引
• 有 290 萬行，而且還在計數……

uj5u.com熱心網友回復：

這是一個很好的例子，用于order by控制distinct on。

select DISTINCT ON (story_id) 
       story_id, "value"
from the_table
where story_id in ('1','2','3')
ORDER BY story_id, "timestamp" desc;

@wildplasser 建議的索引story_id, timestamp將使其擴展良好。

uj5u.com熱心網友回復：

你可以這樣嘗試，它會提高你的表現嗎？

SELECT 
  story_id, 
  value
FROM (
      SELECT 
        story_id, 
        timestamp,
        MAX(timestamp) OVER (PARTITION BY story_id) AS max_timestamp_per_id,
        value
      FROM table)
WHERE timestamp = max_timestamp_per_id

uj5u.com熱心網友回復：

使用分析函式對查詢進行等效重寫以獲取順序中的最后一個IDper如下story_idtimestamp

with last_snap as (
select
story_id, value,
row_number() over (partition by story_id order by timestamp desc)  as rn
from tab
)
select story_id, value
from last_snap
where rn = 1;

這可能比您的子查詢解決方案效果更好，但也不會針對大于 100K 行的資料進行擴展（更好的是，它的擴展方式與對完整資料的排序方式相同）。

具有多個快照的表的正確設定是一個磁區表，其中每個快照包含一個磁區。

該查詢僅選擇最后一個快照( snap_id = nnn) 中的資料，跳過所有舊資料。

uj5u.com熱心網友回復：

• 使用正確的表定義，包括自然鍵(story_id, ztimestamp)。

• BTWtimestamp是一種資料型別，最好不要將其用作列名。

• BTW2：您可能希望story_id成為整數欄位而不是文本欄位，并且由于它是關鍵欄位，您可能還希望它不為空。

-- DDL
DROP TABLE story CASCADE;
CREATE TABLE story
        ( id serial not null primary key
        , story_id text NOT NULL
        , ztimestamp timestamp not null
        , zvalue integer not null default 0
        , UNIQUE (story_id, ztimestamp) -- the natural key
        );


\d  story


EXPLAIN
SELECT * FROM story st
WHERE story_id IN('1','2','3')
AND NOT EXISTS(
        SELECT *
        FROM story nx
        WHERE nx.story_id = st.story_id
        AND nx.ztimestamp > st.ztimestamp
        );

DROP TABLE
CREATE TABLE
                                                              Table "tmp.story"
   Column   |            Type             | Collation | Nullable |              Default              | Storage  | Stats target | Description 
------------ ----------------------------- ----------- ---------- ----------------------------------- ---------- -------------- -------------
 id         | integer                     |           | not null | nextval('story_id_seq'::regclass) | plain    |              | 
 story_id   | text                        |           | not null |                                   | extended |              | 
 ztimestamp | timestamp without time zone |           | not null |                                   | plain    |              | 
 zvalue     | integer                     |           | not null | 0                                 | plain    |              | 
Indexes:
    "story_pkey" PRIMARY KEY, btree (id)
    "story_story_id_ztimestamp_key" UNIQUE CONSTRAINT, btree (story_id, ztimestamp)

                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=1.83..18.97 rows=13 width=48)
   ->  Bitmap Heap Scan on story st  (cost=1.67..10.94 rows=16 width=48)
         Recheck Cond: (story_id = ANY ('{1,2,3}'::text[]))
         ->  Bitmap Index Scan on story_story_id_ztimestamp_key  (cost=0.00..1.67 rows=16 width=0)
               Index Cond: (story_id = ANY ('{1,2,3}'::text[]))
   ->  Index Only Scan using story_story_id_ztimestamp_key on story nx  (cost=0.15..0.95 rows=2 width=40)
         Index Cond: ((story_id = st.story_id) AND (ztimestamp > st.ztimestamp))
(7 rows)

標籤：sql PostgreSQL

上一篇：SQL錯誤[21000]：錯誤：用作運算式的子查詢回傳多行

下一篇：Postgres：值等于十進制值