我如何計算 SQL (Postgres/Vertica) 中的累積百分比？例如，問題是“截至每個日期，在該日期之前被診斷出的所有患者中，在該日期之前接受治療的百分比是多少？”
例如，該表顯示了診斷和治療的日期，二進制值可能會相加
ID | diagnosed | date_diag | treated | date_treat
---|------------|-----------|----------|-----------
1 1 Jan 1 0 null
2 1 Jan 15 1 Feb 20
3 1 Jan 29 1 Feb 1
4 1 Feb 08 1 Mar 4
5 1 Feb 12 0 null
6 1 Feb 18 1 Feb 24
7 1 Mar 15 1 May 5
8 1 Apr 14 1 Apr 20
我想得到一個可能看起來像這樣的累積治療與診斷比率表。
date | ytd_diag | ytd_treat | ytd_percent
-------|------------|-----------|----------
Jan 01 1 0 0.00
Jan 15 2 0 0.00
Jan 29 3 0 0.00
Feb 08 4 1 0.25
Feb 12 5 1 0.20
Feb 18 6 1 0.17
Mar 15 7 4 0.57
Apr 14 8 4 0.50
我可以使用視窗函式計算診斷或治療的累積計數（例如下面），但我無法計算出一個 SQL 查詢來獲取每個診斷日期已經接受治療的人數。
SELECT
date_diag ,
SUM(COUNT(*)) OVER ( ORDER BY date_diag ) as freq
FROM patients
WHERE diagnosed = 1
GROUP BY date_diag
ORDER BY date_diag;
uj5u.com熱心網友回復：
您可以將條件聚合與SUM()視窗函式一起使用：
WITH cte AS (
SELECT kind,
date,
SUM((kind = 1)::int) OVER (ORDER BY date) ytd_diag,
SUM((kind = 2)::int) OVER (ORDER BY date) ytd_treat
FROM (
SELECT 1 kind, date_diag date, diagnosed status FROM patients
UNION ALL
SELECT 2, date_treat, treated FROM patients WHERE date_treat IS NOT NULL
) t
)
SELECT date, ytd_diag, ytd_treat,
ROUND(1.0 * ytd_treat / ytd_diag, 2) ytd_percent
FROM cte
WHERE kind = 1;
請參閱演示。
uj5u.com熱心網友回復：
您可以使用視窗函式解決此問題。您要做的第一件事是從患者表中派生一個表，該表具有診斷和治療列的運行記錄。這些行應按診斷日期的升序排列。
以下是您的操作方法。首先，我將創建一個示例患者表和資料（我將僅包含必要的列）：
create temporary table patients (
date_diag date,
diagnosed int default 0,
treated int default 0
);
insert into patients (date_diag, diagnosed, treated) values
('2021-01-01', 1, 0),
('2021-01-11', 1, 1),
('2021-01-16', 1, 0),
('2021-01-30', 1, 1),
('2021-02-04', 1, 1),
('2021-01-14', 1, 1);
然后這里是如何創建所有統計結果的派生表。
select
date_diag,
diagnosed,
treated,
sum(treated) over(order by date_diag ASC ) as treated_cmtv,
count(diagnosed) over(order by date_diag ASC) as diagnosed_cmtv
from patients
/*
date_diag | diagnosed | treated | treated_cmtv | diagnosed_cmtv
------------ ----------- --------- -------------- ----------------
2021-01-01 | 1 | 0 | 0 | 1
2021-01-11 | 1 | 1 | 1 | 2
2021-01-14 | 1 | 1 | 2 | 3
2021-01-16 | 1 | 0 | 2 | 4
2021-01-30 | 1 | 1 | 3 | 5
2021-02-04 | 1 | 1 | 4 | 6
*/
現在您有了這個表，您可以通過在子查詢中定義這個派生表然后選擇計算所需的列來輕松計算百分比。像這樣：
select
p.date_diag,
p.diagnosed,
p.diagnosed_cmtv,
p.treated_cmtv,
p.treated,
TRUNC(p.treated_cmtv::numeric / p.diagnosed_cmtv * 1.0, 2) as percent
from (
-- same table as above
select
date_diag,
diagnosed,
treated,
sum(treated) over(order by date_diag ASC ) as treated_cmtv,
count(diagnosed) over(order by date_diag ASC) as diagnosed_cmtv
from patients
) as p;
/*
date_diag | diagnosed | diagnosed_cmtv | treated_cmtv | treated | percent
------------ ----------- ---------------- -------------- --------- ---------
2021-01-01 | 1 | 1 | 0 | 0 | 0.00
2021-01-11 | 1 | 2 | 1 | 1 | 0.50
2021-01-14 | 1 | 3 | 2 | 1 | 0.66
2021-01-16 | 1 | 4 | 2 | 0 | 0.50
2021-01-30 | 1 | 5 | 3 | 1 | 0.60
2021-02-04 | 1 | 6 | 4 | 1 | 0.66
*/
我認為這可以滿足您的要求。
uj5u.com熱心網友回復：
其他答案的另一種方法是在選擇中使用協調的子查詢
SELECT
p.date_diag,
(SELECT COUNT(*)
FROM patients p2
WHERE p2.date_treat <= p.date_diag) ytd_treated
FROM
patients p
WHERE diagnosed = 1
GROUP BY p.date_diag
ORDER BY p.date_diag
這將為您提供 0,0,0,1,1,4,4 列 - 您可以將其除以診斷列以給出您的百分比
SELECT
(select ...) / SUM(COUNT(*)) OVER(...)
請注意，如果您針對包含一年以上資料的資料集運行它，則可能需要在內部 where 中添加更多子句，例如處理日期大于或等于診斷日期當年的 1 月 1 日
還要記住，被視為整數將（應該）幾乎總是小于診斷，所以如果你做一個整數除法你會得到零。將其中一個運算元轉換為浮點數，或者如果您正在完成百分百的百分比*100.0
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/437311.html
標籤：sql PostgreSQL
下一篇：為什么在docker(ubuntu:18.04)中運行長時間運行的postgres查詢需要`psycopg2.connect(...)`中的`keepalives`引數？