在 Vertica 中运行递归查询 [英] Running recursive query in Vertica

问题描述

我正在尝试做与这个问题完全相同的事情.但是我在 Vertica 中，我找不到执行最佳答案或其他答案的方法.所以基本上我已经尝试了connect by 和子查询UNION ALL 方法，我认为Vertica 不支持它.

I am trying to do the exact same thing as this question. But I am in Vertica, and I am finding no way to carry out the top answer, or the other answers. So basically I have tried connect by and sub query UNION ALL method, and I don't think Vertica supports it.

有什么方法可以在 Vertica 中复制解决方案?

Is there any way I can replicate the solution in Vertica?

完整问题

我正在尝试计算 30 天的再入院链，这是从上次入院后 30 天内的再入院序列.以下数据显示了我们有事件而不是入院和出院的简化情况.事件之间的天数差异将其识别为 30 天再入院，连续 30 天再入院(链 Len)将是单个再入院链(计数).

I am trying to calculate 30-day readmission chains, which is a sequence of readmissions within 30 days from its previous admission. The following data shows a simplified situation where we have events, rather than admission and discharges. Difference in days between events will identify it as a 30-day readmission, consecutive 30 day readmissions(Chain Len) will be a single chain of readmission (Count).

样本数据

CREATE TABLE dbo.Events (
    EventID INT IDENTITY(1,1) PRIMARY KEY,
    EventDate DATE NOT NULL,
    PersonID INT NOT NULL
);
GO
INSERT dbo.Events (EventDate, PersonID)
VALUES 
    ('2014-01-01', 1), ('2014-01-05', 1), ('2014-02-02', 1), ('2014-03-30', 1), ('2014-04-04', 1), 
    ('2014-01-11', 2), ('2014-02-02', 2),
    ('2014-01-03', 3), ('2014-03-03', 3);
GO

样本输出

EventID EventDate  PersonID CHAIN LEN Count
------- ---------- -------- --------- -----
1       2014-01-01 1        1         1
2       2014-01-05 1        2         1
3       2014-02-02 1        3         1
------- ---------- -------- --------- -----
4       2014-03-30 1        1         2
5       2014-04-04 1        2         2
------- ---------- -------- --------- -----
6       2014-01-11 2        1         1
7       2014-02-02 2        2         1
------- ---------- -------- --------- -----
8       2014-01-03 3        1         1
------- ---------- -------- --------- -----
9       2014-03-03 3        1         2
------- ---------- -------- --------- -----

推荐答案

这是一个 Oracle 解决方案；看看它是否有效.您可能需要对 vertica 进行一些更改，因为每个 db 方言都有自己的怪癖.Vertica 确实支持分析功能，这是主要成分.

Here is an Oracle solution; see if it works. You may need to make some changes for vertica, as each db dialect has its own quirks. Vertica does support analytic functions, which is the main ingredient.

这里使用的方法是一个非常有名的方法，通常称为组开始"方法(用于最内层子查询中创建的标志").

The method used here is a very well known one, it is usually called "start-of-groups" method (for the "flags" created in the innermost subquery).

select eventid, eventdate, personid,
       row_number() over 
         (partition by personid, ct order by eventdate) as chain_len,
       ct
from   (
         select eventid, eventdate, personid,
                count(flag) over 
                  (partition by personid order by eventdate) + 1 as ct
         from   (
                  select eventid, eventdate, personid,
                         case when eventdate > lag(eventdate) over 
                              (partition by personid order by eventdate) + 30  
                              then 0 end as flag
                  from   events
                )
       )
order by personid, eventdate  -- if needed
;

这篇关于在 Vertica 中运行递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！