通过SQL根据日期合并（连接）行 [英] Combine(concatenate) rows based on dates via SQL

问题描述

我有下表。

 动物疫苗日期疫苗
狗1/1/2016 x 
狗2/1/2016 y 
狗2/1/2016 z 
猫2/1/2016 y 
猫2/1/2016 z 
 
 动物疫苗_日期疫苗
狗1/1/2016 x 
狗2/1/2016 y，z 
猫2/1/2016 y，z 
  
我曾尝试创建一个易失性表来执行此操作，但我没有运气，而且我认为Teradata无法识别Group_concat。
解决方案
 已更新20180419  
 
 
 
 Teradata（不确定哪个版本）已添加 XMLAGG 在此比递归是一个更好的选择）
 
 
 
 
 
 
 原始答案： 
 
 
 
 Teradata没有 group_concat  /  listagg 功能。有几种解决方法。我最喜欢的是使用递归CTE。它的效率不是很高，但是有充分的文档记录和受支持的功能。
 
 
 
在您的情况下：
 具有递归记录作为
（
选择
动物，
疫苗日期，
 CAST（min（疫苗）为VARCHAR（50））作为疫苗列表，-大到足以容纳连接列表
 1作为深度，-用于确定最终SELECT 
疫苗中的最大/最后一个group_concate（完整组）
 
 FROM表
 GROUP BY 1,2 
 
 
 UNION ALL 
 
 SELECT 
 recCTE.Animal，
 recCTE.Vaccine_Date，
 recCTE.Vaccine ||'，'||表。疫苗
 recCTE.depth +，
表。疫苗
 FROM recCTE 
 INNER JOIN表在
上recCTE.Animal = table.Animal AND 
 recCTE.Vaccine_Date = Table.Vaccine_Date 
 table.vaccine> recCTE.vaccine 
）
 
-现在为每只动物/ vaccine_date组合
选择深度最大的结果* FROM recCTE 
 QUALIFY ROW_NUMBER（）OVER（按动物划分，vaccine_date按深度desc排序）= 1 
  
您可能需要稍微调整一下（可能在连接前先降低疫苗的值，然后再进行其他操作），但它应该使您陷入困境。您可以查看递归CTE文档在此链接上，但还很干燥。如果您不熟悉，那里也有很多教程。 Teradata递归CTE的实现与T-SQL和PostgresSQL的实现也非常相似。
 
 
 
作为另一种选择，您可以查看尚未记录的 tdstats.udfconcat（），如此线程在Teradata社区网站上。
 
I have the following table.
Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y
Dog     2/1/2016        z
Cat     2/1/2016        y
Cat     2/1/2016        z
I want to be able to combine vaccines that are on the same animal and same date, so that they appear in the same cell.  The table below is what the desired end result would be.
Animal  Vaccine_Date    Vaccine
Dog     1/1/2016        x
Dog     2/1/2016        y,z
Cat     2/1/2016        y,z
I have tried to create a volatile table to do so but I am not having any luck and I don't think Teradata recognizes the Group_concat.
解决方案
UPDATED 20180419

Teradata (not sure which version) has added XMLAGG which would be a better choice here than recursive)



Original answer:

Teradata doesn't have group_concat/listagg functionality. There are a couple of workarounds. My favorite is to use a recursive CTE. It's not terribly efficient, but it's well documented and supported functionality.

In your case:
WITH RECURSIVE recCTE AS
    (
        SELECT 
            Animal, 
            Vaccine_Date,
            CAST(min(Vaccine) as VARCHAR(50)) as vaccine_list, --big enough to hold concatenated list 
            1 as depth, --used to determine the largest/last group_concate (the full group) in the final SELECT
            Vaccine

        FROM table
        GROUP BY 1,2


        UNION ALL

        SELECT 
            recCTE.Animal,
            recCTE.Vaccine_Date,
            recCTE.Vaccine || ',' || table.Vaccine
            recCTE.depth + ,
            table.Vaccine
        FROM recCTE 
            INNER JOIN table ON
                recCTE.Animal = table.Animal AND
                recCTE.Vaccine_Date =  Table.Vaccine_Date
                table.vaccine > recCTE.vaccine
    )

    --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
You may have to tweak that a little bit (possibly trim the vaccine values before concatenating and whatnot), but it should get you in the ballpark. You can check out the recursive CTE documentation at this link, but it's pretty dry. There are a lot of tutorials out there too, if you are unfamiliar. Teradata's implementation of recursive CTE is very similar to T-SQL and PostgresSQL's implementation as well.

As another option you can check out the as-of-yet undocumented tdstats.udfconcat() as explained by the extremely knowledgable @dnoeth in this thread on Teradata Community website.

                        
这篇关于通过SQL根据日期合并（连接）行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！