選擇DISTINCT但不要弄亂行的順序

如果你想玩我的資料，我在帖子的最后添加了一個游樂場。謝謝你。

架構

CREATE TABLE "public"."posts" (
    "id" bigint NOT NULL,
    "text" "text",
    "user_id" "uuid" NOT NULL
);

INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (173, 'iOS created', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (174, 'chrome reply', '90a42a87-9f9b-4676-bc6a-214be0050053');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (175, 'brave reply', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (176, '@1 chrome mention', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (177, '@0 ios mention', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (178, '', '00bf9dae-e5a7-4f86-a3fd-0be4630cffd4');
INSERT INTO "public"."posts" ("id", "text", "user_id" ) VALUES (179, '@2 mention brave', '9361a62b-462a-4ca3-ba1d-e0c2e0174aa0');

詢問

select * from
(
  select 
  --DISTINCT ON (user_id)
  text,
  row_number() over() rank,
  user_id
  from
  (
    SELECT 
    id,
    text,
    user_id
    FROM posts
    order by id asc
  ) alias1
) alias2

做我想要的，但我想 GROUP BYuser_id并盡可能保持最低rank。當使用感覺隨機時，DISTINCT (user_id) *這rank就是問題所在。

--DISTINCT ON (user_id)在操場上取消注釋以查看rank隨機行而不是第一rank行。

輸出無 DISTINCT

rank    user_id text
1   9361a62b-462a-4ca3-ba1d-e0c2e0174aa0    iOS created
2   90a42a87-9f9b-4676-bc6a-214be0050053    chrome reply
3   00bf9dae-e5a7-4f86-a3fd-0be4630cffd4    brave reply
4   9361a62b-462a-4ca3-ba1d-e0c2e0174aa0    @1 chrome mention
5   00bf9dae-e5a7-4f86-a3fd-0be4630cffd4    @0 ios mention
6   00bf9dae-e5a7-4f86-a3fd-0be4630cffd4    
7   9361a62b-462a-4ca3-ba1d-e0c2e0174aa0    @2 mention brave

輸出與 DISTINCT

rank    user_id text
5   00bf9dae-e5a7-4f86-a3fd-0be4630cffd4    @0 ios mention
2   90a42a87-9f9b-4676-bc6a-214be0050053    chrome reply
4   9361a62b-462a-4ca3-ba1d-e0c2e0174aa0    @1 chrome mention

我需要的輸出：

rank    user_id text
1   9361a62b-462a-4ca3-ba1d-e0c2e0174aa0    iOS created
2   90a42a87-9f9b-4676-bc6a-214be0050053    chrome reply
3   00bf9dae-e5a7-4f86-a3fd-0be4630cffd4    brave reply

用我的資料查詢：http ://sqlfiddle.com/#!15/df83a/2/0

uj5u.com熱心網友回復：

關系資料庫中的表、視圖、查詢結果等表示關系。關系是（多）集合的一種特殊形式。套裝沒有順序。因此，除非有明確的. 這就是你得到“隨機”結果的原因。ORDER BY

所以使用適當的ORDER BY子句 forDISTINCT ON和row_number()。使用帶有 an 的外部查詢ORDER BY對最終結果進行排序。

SELECT rank,
       user_id,
       text
       FROM (SELECT DISTINCT ON (user_id)
                    row_number() OVER (ORDER BY id) AS rank,
                    user_id,
                    text
                    FROM posts
                    ORDER BY user_id,
                             id) AS x
       ORDER BY rank;

db<>小提琴

uj5u.com熱心網友回復：

您可以通過以下方式將子查詢與組一起使用：

select posts.* from posts
inner join
(
  select 
  user_id, min(id) as minId
  from posts
  group by user_id
) p on p.user_id = posts.user_id and p.minId = posts.Id;

編輯：擁有你稱之為“等級”但實際上是 row_number 的東西：

select row_number() over (order by posts.id) as rank, 
   posts.user_id, posts.text from posts
inner join
(
  select 
  user_id, min(id) as minId
  from posts
  group by user_id
) p on p.user_id = posts.user_id and p.minId = posts.Id;

標籤：

上一篇：找出在經理手下作業的員工人數

下一篇：PrestoSQL類別計數器