oracle 获取在每个不同日期之前仍然为阳性(已感染)的不同用户的总数

kuhbmx9i 于 2023-03-29 发布在 Oracle

关注(0)|答案(2)|浏览(112)

我有一个跟踪拭子结果的表格。每个用户可以在不同的日期采集多个拭子（精确到秒），得到不同的结果（“Y”-〉阳性，“N”-〉阴性）。
表如下：

CREATE TABLE swabs
(
    id          INTEGER    PRIMARY KEY,
    user_id     INTEGER    NOT NULL,
    date_result DATE       NOT NULL,
    positivity  CHAR(1)    NOT NULL,

    CONSTRAINT chk_swabs CHECK (positivity IN ('Y', 'N')),
    CONSTRAINT uc_swabs UNIQUE (user_id, date_result),
    CONSTRAINT fk_swabs_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

下面是一些模拟插入后的表内容：

| ID       | USER_ID  | DATE_RESULT          | POSITIVITY |
| -------- | -------- | -------------------- | ---------- |
| 1        | 1        | 2023-03-18 23:59:57  | Y          |
| 2        | 2        | 2023-03-18 23:59:58  | Y          |
| 3        | 3        | 2023-03-18 23:59:59  | Y          |
| 4        | 2        | 2023-03-19 23:59:56  | N          |
| 5        | 3        | 2023-03-19 23:59:57  | N          |
| 6        | 4        | 2023-03-19 23:59:58  | Y          |
| 7        | 7        | 2023-03-19 23:59:59  | Y          |
| 8        | 5        | 2023-03-20 23:59:57  | Y          |
| 9        | 6        | 2023-03-20 23:59:58  | Y          |
| 10       | 2        | 2023-03-20 23:59:59  | Y          |
| 11       | 1        | 2023-03-21 23:59:57  | N          |
| 12       | 4        | 2023-03-21 23:59:58  | N          |
| 13       | 7        | 2023-03-21 23:59:59  | N          |
| 14       | 1        | 2023-03-22 23:59:56  | Y          |
| 15       | 2        | 2023-03-22 23:59:57  | Y          |
| 16       | 3        | 2023-03-22 23:59:58  | Y          |
| 17       | 4        | 2023-03-22 23:59:59  | Y          |

我的目标是**计算表中存在的每个不同日期的不同用户的数量，直到每个日期，仍然是正数。
结果应如下所示：

| DATE_DISTINCT | TOTAL_INFECTED |
| ------------- | -------------- |
| 2023-03-18    | 3              |
| 2023-03-19    | 3              |
| 2023-03-20    | 6              |
| 2023-03-21    | 3              |
| 2023-03-22    | 6              |

到目前为止，我所实现的是一个查询，它可以在给定的输入日期之前计算出在给定日期之前仍然为正的用户总数。
此查询检索截至“2023-03- 20”的受感染用户数：

SELECT COUNT(positivity)
FROM swabs s
         INNER JOIN (SELECT user_id,
                            MAX(date_result) AS max_date
                     FROM swabs
                     WHERE date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
                     GROUP BY user_id) m ON s.user_id = m.user_id AND s.date_result = m.max_date
WHERE s.date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
  AND s.POSITIVITY = 'Y';

我想在每一天都“执行”这个查询。
这是正确的做法吗？

oracle

来源：https://stackoverflow.com/questions/75869780/get-for-each-distinct-date-the-total-number-of-distinct-users-that-up-until-that

2条答案

按热度按时间

fcwjkofz1#

我不确定我得到了你想得到的，但如果我没有误解你需要计算每一天的不同的积极用户

SELECT TRUNC(DATE_RESULT) AS DAY, COUNT(DISTINCT CASE WHEN POSITIVITY = 'N' THEN NULL ELSE USER_ID END) AS TOTAL_INFECTED
 GROUP BY TRUNC(DATE_RESULT)

赞(0）回复(0）举报 2023-03-29

cwtwac6a2#

您可以用途：

WITH dates (day, user_id, positivity) AS (
  SELECT TRUNC(date_result),
         user_id,
         MAX(positivity) KEEP (DENSE_RANK LAST ORDER BY date_result)
  FROM   swabs
  GROUP BY
         TRUNC(date_result),
         user_id
),
date_range (start_day, end_day) AS (
  SELECT MIN(day), MAX(day)
  FROM   dates
),
calendar (day) AS (
  SELECT start_day + LEVEL - 1
  FROM   date_range
  CONNECT BY start_day + LEVEL - 1 <= end_day
),
results (day, user_id, positivity) AS (
  SELECT c.day,
         d.user_id,
         LAST_VALUE(d.positivity IGNORE NULLS)
           OVER (PARTITION BY d.user_id ORDER BY c.day)
  FROM   calendar c
         LEFT OUTER JOIN dates d
         PARTITION BY (d.user_id)
         ON (c.day = d.day)
)
SELECT day,
       COUNT(CASE positivity WHEN 'Y' THEN 1 END) AS num_positive
FROM   results
GROUP BY day
ORDER BY day

其中，对于示例数据：

CREATE TABLE swabs ( ID, USER_ID, DATE_RESULT, POSITIVITY ) AS
SELECT  1, 1, DATE '2023-03-18' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  2, 2, DATE '2023-03-18' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  3, 3, DATE '2023-03-18' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  4, 2, DATE '2023-03-19' + INTERVAL '23:59:56' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT  5, 3, DATE '2023-03-19' + INTERVAL '23:59:57' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT  6, 4, DATE '2023-03-19' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  7, 7, DATE '2023-03-19' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  8, 5, DATE '2023-03-20' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT  9, 6, DATE '2023-03-20' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 10, 2, DATE '2023-03-20' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 11, 1, DATE '2023-03-21' + INTERVAL '23:59:57' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 12, 4, DATE '2023-03-21' + INTERVAL '23:59:58' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 13, 7, DATE '2023-03-21' + INTERVAL '23:59:59' HOUR TO SECOND, 'N' FROM DUAL UNION ALL
SELECT 14, 1, DATE '2023-03-22' + INTERVAL '23:59:56' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 15, 2, DATE '2023-03-22' + INTERVAL '23:59:57' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 16, 3, DATE '2023-03-22' + INTERVAL '23:59:58' HOUR TO SECOND, 'Y' FROM DUAL UNION ALL
SELECT 17, 4, DATE '2023-03-22' + INTERVAL '23:59:59' HOUR TO SECOND, 'Y' FROM DUAL;

输出：
| 日|阳性数量|
| --------------|--------------|
| 2023年3月18日00时00分|三|
| 2023年3月19日00时00分|三|
| 2023年3月20日00时00分|六|
| 2023年3月21日00时00分|三|
| 2023年3月22日00时00分|六|
fiddle

赞(0）回复(0）举报 2023-03-29

我来回答

oracle 获取在每个不同日期之前仍然为阳性(已感染)的不同用户的总数

2条答案

相关问题

热门标签

最新问答