通过递归查询组合SQL中的行

Question

我有以下表格:

Animal  Vaccine_Date    Vaccine
Cat     2/1/2016        y
Cat     2/1/2016        z
Dog     2/1/2016        z
Dog     1/1/2016        x
Dog     2/1/2016        y

我希望得到如下所示的结果。

Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y,z
Cat     2/1/2016        y,z

我有以下代码，这是通过我的其他帖子提供的"通过SQL结合(连接)基于日期的行"

WITH RECURSIVE recCTE AS
(
    SELECT 
        animal, 
        vaccine_date,
        CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list 
        cast (1 as int) as depth --used to determine the largest/last group_concate (the full group) in the final select
    FROM TableOne
    GROUP BY 1,2

    UNION ALL
    SELECT 
        recCTE.animal,
        recCTE.vaccine_date,
        trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
        recCTE.depth + cast(1 as int) as depth
    FROM recCTE 
        INNER JOIN TableOne ON
            recCTE.animal = TableOne.animal AND
            recCTE.vaccine_date =  TableOne.vaccine_date and
           TableOne.vaccine > recCTE.vaccine
           WHERE recCTE.depth < 5

)
--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1

但是这会导致如下结果:

Animal  Vaccine_Date    vaccine     depth
Cat     2/1/2016        y,z,z,z,z   5
Dog     1/1/2016        x           1
Dog     2/1/2016        y,z,z,z,z   5

"z"不断重复。这是因为代码说的是比最低疫苗量更大的东西。为了说明这一点，代码更改为以下内容:

WITH RECURSIVE recCTE AS
(
    SELECT 
        animal, 
        vaccine_date,
        CAST(min(vaccine) as VARCHAR(50)) as vaccine, --big enough to hold concatenated list 
        cast (1 as int) as depth, --used to determine the largest/last group_concate (the full group) in the final select
        vaccine as vaccine_check
    FROM TableOne
    GROUP BY 1,2,5

    UNION ALL
    SELECT 
        recCTE.animal,
        recCTE.vaccine_date,
        trim(trim(recCTE.vaccine)|| ',' ||trim(TableOne.vaccine)) as vaccine,
        recCTE.depth + cast(1 as int) as depth,
        TableOne.vaccine as vaccine_check
    FROM recCTE 
        INNER JOIN TableOne ON
            recCTE.animal = TableOne.animal AND
            recCTE.vaccine_date =  TableOne.vaccine_date and
           TableOne.vaccine > recCTE.vaccine and
           vaccine_check <> recCTE.vaccine_check 
           WHERE recCTE.depth < 5

)
--Now select the result with the largest depth for each animal/vaccine_date combo
SELECT * FROM recCTE
QUALIFY ROW_NUMBER() OVER (PARTITION BY animal,vaccine_date ORDER BY depth desc) =1

然而，这导致了如下结果:

Animal  Vaccine_Date    vaccine depth   vaccine_check
Cat     2/1/2016        y       1       y
Dog     1/1/2016        x       1       x
Dog     2/1/2016        y       1       y

代码中缺少什么来获得下面的期望结果

Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y,z
Cat     2/1/2016        y,z

Answer 1

嗯。我手头没有Teradata，但这是这个项目的一个主要缺点(在我看来)。我认为这将为您工作，但它可能需要一些调整:

with tt as (
      select t.*,
             row_number() over (partition by animal, vaccine_date order by animal) as seqnum
             count(*) over (partition by animal, vaccine_date) as cnt
     ),
     recursive cte as (
      select animal, vaccine_date, vaccine as vaccines, seqnum, cnt
      from tt
      where seqnum = 1
      union all
      select cte.animal, cte.dte, cte.vaccines || ',' || t.vaccine, tt.seqnum, tt.cnt
      from cte join
           tt
           on tt.animal = cte.animal and
              tt.vaccine_date = cte.vaccine_date and
              tt.seqnum = cte.seqnum + 1
     )
select cte.*
from cte
where seqnum = cnt;

Answer 2

如果您的Teradata数据库版本是14.10或更高版本，它支持XML数据类型。这也意味着支持XMLAGG函数，这对您的情况很有用，并且可以让您避免递归。

检查XMLAGG函数是否存在，该函数作为UDF与XML Services一起安装:

SELECT * FROM dbc.FunctionsV WHERE FunctionName = 'XMLAGG'

如果是，那么查询看起来像:

SELECT
  animal,
  vaccine_date
  TRIM(TRAILING ',' FROM CAST(XMLAGG(vaccine || ',' ORDER BY vaccine) AS VARCHAR(10000)))
FROM
  tableone
GROUP BY 1,2

我没有办法测试这个atm，但我相信这应该工作与可能性的小调整。

Answer 3

我能够使用以下SQL获得所需的结果。这似乎不是很有效，也不是动态的。但是，我可以根据需要添加额外的子查询，以按动物和日期组合更多的疫苗。

select 
qrya.animal
,qrya.vaccine_date
,case when qrya.vac1 is not null then qrya.vac1 else null end ||','||case when qrya.animal=qryb.animal and qrya.vaccine_date=qryb.vaccine_date then qryb.Vac2 else 'End' end as  vaccine_List
from
(
select
qry1.Animal
,qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 1  then qry1.vaccine end as Vac1

from
(
select 
animal
,vaccine_date
,vaccine
,row_number() over (partition by animal,vaccine_date order by vaccine) as Vaccine_Rank
from TableOne
) as qry1
where vac1 is not null
group by qry1.Animal,
qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 1  then qry1.vaccine end 
) as qrya
join
(
select
qry1.Animal
,qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 2  then qry1.vaccine end as Vac2

from
(
select 
animal
,vaccine_date
,vaccine
,row_number() over (partition by animal,vaccine_date order by vaccine) as Vaccine_Rank
from TableOne
) as qry1
where vac2 is not null
group by qry1.Animal,
qry1.Vaccine_Date
,case when qry1.Vaccine_Rank = 2  then qry1.vaccine end 
) as qryb
on qrya.Animal=qryb.Animal