在列中顯示前10個出現的值

想知道如何查詢列中值的前 10 次出現。

架構如下：

CREATE TABLE Donations (
  id varchar(255) PRIMARY KEY,
  charityId varchar(255) NOT NULL,
  amount integer, 
  createdAt timestamp,
  updatedAt timestamp
);

假設這張表有 1m 條記錄。想知道如何獲得一個輸出，該輸出將回傳包含所有記錄中前 10 個charityId 的行；以及包含該charityId 的記錄總數。

示例資料如下所示：

| id   | charity id  | amount | created at                   | updated at                   |
|------|-------------|--------|------------------------------|------------------------------|
| "1"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "2"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "3"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "4"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "5"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "6"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "7"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "8"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "9"  | "charity-1" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "10" | "charity-2" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "11" | "charity-2" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "12" | "charity-2" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "13" | "charity-2" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "14" | "charity-3" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "15" | "charity-3" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "16" | "charity-3" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "17" | "charity-4" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "18" | "charity-4" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "19" | "charity-5" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "20" | "charity-5" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |
| "21" | "charity-6" | 100    | "2022-03-21 04:08:53.848451" | "2022-03-21 04:08:53.848451" |

理想的輸出是這樣的：

| charity id  | occurrences                  |
|-------------|------------------------------|
| "charity-1" | 9                            |
| "charity-2" | 4                            |
| "charity-3" | 3                            |
| "charity-4" | 2                            |
| "charity-5" | 2                            |

uj5u.com熱心網友回復：

由于沒有定義決勝局，因此回傳結果“with ties”：

SELECT charityid, count(*) AS occurrencs
FROM   donations
GROUP  BY charityid
ORDER  BY occurrencs DESC  -- no tiebeaker?
FETCH  FIRST 10 ROWS WITH TIES;

看：

• 獲取具有最高值的頂行，與領帶

并count(*)在這里使用。更快，等效，但不null涉及任何值。

uj5u.com熱心網友回復：

通過使用 group by ， count ：

SELECT charityId , count(charityId )  As Occurrencs 
    from Donations 
    group by charityId 
    Order by Occurrencs Desc , charityId 

uj5u.com熱心網友回復：

基本查詢：

• 按“charityId”聚合
• 計算行數
• 按計數順序排序
• 僅保留前 10 條記錄

SELECT "charityId" as "charity id", count(*)  As "occurrences" 
FROM "Donations" 
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

標籤：sql PostgreSQL

上一篇：獲取具有相關季節串列的用戶

下一篇：在PostgreSQL中更改計算的資料型別