postgres中多行的第一列值

我正在使用 postgres 11，并有下表

 ---------- ------------------- ----------- ----------------------------- 
| username |      filters      |   flag    |     time                    |
 ---------- ------------------- ----------- ----------------------------- 
| user1    | filter001         | 0         |2022-06-16 05:35:19.593000   |
| user1    | filter001         | 0         |2022-06-16 05:35:19.603000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.753000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.763000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.773000   |
| user1    | filter001         | 0         |2022-06-16 05:35:19.793000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.793000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.813000   |
| user1    | filter002         | 0         |2022-06-16 05:35:19.823000   |
| user1    | filter002         | 0         |2022-06-16 05:35:19.833000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.843000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.853000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.863000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.873000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.883000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.893000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.903000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.913000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.923000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.933000   |
| user1    | filter004         | 1         |2022-06-16 05:35:19.943000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.953000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.963000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.973000   |
 ---------- ------------------- ----------------------------------------- 

我正在嘗試從不同的過濾器值以及計數計算中獲得以下結果

 ----------- ---------- --------- --------------- ---------------------------- 
|  filters  | total_0  | total_1 | total_0_and_1 |    time                    |
 ----------- ---------- --------- --------------- ---------------------------- 
| filter001 |    3     | 3       |      6        |2022-06-16 05:35:19.593000  |
| filter002 |    2     | 4       |      6        |2022-06-16 05:35:19.793000  |
| filter003 |    4     | 3       |      7        |2022-06-16 05:35:19.863000  |
| filter004 |    4     | 1       |      5        |2022-06-16 05:35:19.933000  |
 ----------- ---------- --------- --------------- ---------------------------- 

我嘗試了下面的查詢，除了時間值之外，它給了我想要的結果，我無法將時間值添加到唯一的過濾器，時間值可能是每個過濾器的第一個記錄值。有什么方法可以優化和添加每個過濾器的第一條記錄的時間值？

select filters, 
       count(flag) filter (where flag=0) as total_0 ,
       count(flag) filter (where flag=1) as total_1, 
       count(time) as total_0_and_1 
from My_Table 
where username='user1';

uj5u.com熱心網友回復：

我們可以將這里COUNT用作分析函式DISTINCT ON：

WITH cte AS (
    SELECT COUNT(flag) FILTER (WHERE flag = 0) OVER (PARTITION BY filters) AS total_0,
           COUNT(flag) FILTER (WHERE flag = 1) OVER (PARTITION BY filters) AS total_1, 
           COUNT(time) OVER (PARTITION BY filters) AS total_0_and_1,
           filters, time
    FROM My_Table 
    WHERE username = 'user1'
)

SELECT DISTINCT ON (filters) filters, total_0, total_1, total_0_and_1, time
FROM cte
ORDER BY filters, time;

標籤：PostgreSQL postgres-11

上一篇：池化NpgsqlConnection與PostgreSQL會話的關系

下一篇：Postgres14中用戶repl的正確權限