时间戳汇总 [英] Aggregation by timestamp

查看：93 发布时间：2020/6/2 20:46:29 sql aggregate-functions teradata

本文介绍了时间戳汇总的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SEO> SEO>付费1
付费>付费>会员>付费1
SEO>会员1我有一个查询，该查询的结果包含客户ID号，营销渠道，时间戳和购买日期。因此，结果可能看起来像这样。

SEO > SEO > Paid 1 Paid > Paid > Affiliate > Paid 1 SEO > Affiliate 1I have a query that results in a data containing customer id numbers, marketing channel, timestamp, and purchase date. So, the results might look something like this.

id marketingChannel TimeStamp      Transaction_date
1  SEO              5/18 23:11:43  5/18
1  SEO              5/18 24:12:43  5/18
1  Paid             5/18 24:13:43  5/18
2  Paid             5/18 24:12:43  5/18
2  Paid             5/18 24:14:43  5/18
2  Affiliate        5/18 24:20:43  5/18
2  Paid             5/18 24:22:43  5/18
3  SEO              5/18 24:10:43  5/18
3  Affiliate        5/18 24:11:43  5/18

我想知道是否存在查询来以显示营销路径计数的方式聚合此信息。

I'm wondering if there is a query to aggregate this information in a fashion that show the count of marketing paths.

例如。

Marketing Path                  Count
SEO > SEO > Paid                  1
Paid > Paid > Affiliate > Paid    1
SEO > Affiliate                   1

我正在考虑编写Python脚本来获取此信息，但我想知道是否有是SQL中的简单解决方案-因为我不熟悉SQL。

I'm thinking about writing a Python script to get this information, but am wondering if there is a simple solution in SQL - as I'm not as framiliar with SQL.

推荐答案

几年前，我需要类似的结果我测试了在Teradata中获取串联字符串的不同方法。顺便说一句，如果行数太多并且连接的字符串超过64000个字符，所有操作都可能失败。

Some years ago I needed a similar result and I tested different ways to get a concatenated string in Teradata. Btw, all might fail if the number of rows is too high and the concatenated string exceeds 64000 chars.

最有效的是用户定义函数（用C编写）：

The most efficient was a User Defined Function (written in C):

SELECT
   PATH
  ,COUNT(*)
FROM
 (
   SELECT 
      DelimitedBuildSorted(MARKETINGCHANNEL
                          ,CAST(CAST(ts AS FORMAT 'yyyymmddhhmiss') AS VARCHAR(14))
                          ,'>') AS PATH
   FROM t
   GROUP BY id
 ) AS dt
GROUP BY 1;

如果您需要经常和/或在大型表上运行该查询，则可以与您的DBA联系如果可以使用UDF（大多数DBA不喜欢它们，因为它们是用他们不知道的语言编写的，C）。

If you need to run that query frequently and/or on a large table you might talk to your DBA if a UDF is possible (most DBAs don't like them as they're written in a language they don't know, C).

如果每个id的平均行数很低。
Joseph B的版本可以简化一些，但是最重要的是创建一个临时表，而不是使用View或Derived Table进行ROW_NUMBER计算。这样会导致更好的计划（在SQL Server中也是如此）：

Recursion might be ok if the average number of rows per id is low. Joseph B's version can be a bit simplified, but the most important thing is to create a temporary table instead of using a View or Derived Table for the ROW_NUMBER calculation. This results in a better plan (in SQL Server, too):

CREATE VOLATILE TABLE vt AS 
 (
   SELECT
      id
     ,MarketingChannel
     ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY TS DESC) AS rn
     ,COUNT(*) OVER (PARTITION BY id) AS max_rn
   FROM t
 ) WITH DATA 
PRIMARY INDEX (id) 
ON COMMIT PRESERVE ROWS;

WITH RECURSIVE cte(id, path, rn) AS
 (
   SELECT 
      id, 

      -- modify VARCHAR size to fit your maximum number of rows, that's better than VARCHAR(64000)
      CAST(MarketingChannel AS VARCHAR(10000)) AS PATH, 
      rn
   FROM vt
   WHERE rn = max_rn
   UNION ALL
   SELECT 
      cte.ID, 
      cte.PATH || '>' || vt.MarketingChannel, 
      cte.rn-1
   FROM vt JOIN cte
     ON vt.id = cte.id
    AND vt.rn = cte.rn - 1
 )
SELECT 
   PATH, 
   COUNT(*) 
FROM cte
WHERE rn = 1
GROUP BY path
ORDER BY PATH
;

您也可以尝试旧学校MAX（CASE）：

You might also try old school MAX(CASE):

SELECT
   PATH
  ,COUNT(*)
FROM
 (
   SELECT
      id
     ,MAX(CASE WHEN rnk =  0 THEN MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  1 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  2 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  3 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  4 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  5 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  6 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  7 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  8 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk =  9 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 10 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 11 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 12 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 13 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 14 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 15 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 16 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 17 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 18 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 19 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 20 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 21 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 22 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 23 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 24 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 25 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 26 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 27 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 28 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 29 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 30 THEN '>' || MarketingChannel ELSE '' END) ||
      MAX(CASE WHEN rnk = 31 THEN '>' || MarketingChannel ELSE '' END) AS PATH
   FROM
    (
     SELECT
        id
       ,TRIM(MarketingChannel) AS MarketingChannel
       ,RANK() OVER (PARTITION BY id
                     ORDER BY TS) -1 AS rnk
     FROM t
    ) dt
   GROUP BY 1
 ) AS dt
GROUP BY 1;

我最多可以合并2048行，每行30个字符：-）

I had up to concat 2048 rows with 30 chars each :-)

SELECT
   PATH
  ,COUNT(*)
FROM
 (
   SELECT
      id
     ,MAX(CASE WHEN rnk MOD 16 = 0 THEN path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 1 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 2 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 3 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 4 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 5 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 6 THEN '>' || path ELSE '' END) ||
      MAX(CASE WHEN rnk MOD 16 = 7 THEN '>' || path ELSE '' END) AS PATH
   FROM
    (
     SELECT
        id
       ,rnk / 16 AS rnk
       ,MAX(CASE WHEN rnk MOD 16 =  0 THEN path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  1 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  2 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  3 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  4 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  5 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  6 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  7 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  8 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 =  9 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 10 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 11 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 12 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 13 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 14 THEN '>' || path ELSE '' END) ||
        MAX(CASE WHEN rnk MOD 16 = 15 THEN '>' || path ELSE '' END) AS path
     FROM
      (
       SELECT
          id
         ,rnk / 16 AS rnk
         ,MAX(CASE WHEN rnk MOD 16 =  0 THEN path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  1 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  2 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  3 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  4 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  5 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  6 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  7 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  8 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 =  9 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 10 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 11 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 12 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 13 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 14 THEN '>' || path ELSE '' END) ||
          MAX(CASE WHEN rnk MOD 16 = 15 THEN '>' || path ELSE '' END) AS path
       FROM
        (
         SELECT
            id
           ,TRIM(MarketingChannel) AS PATH
           ,RANK() OVER (PARTITION BY id
                         ORDER BY TS) -1 AS rnk
         FROM t
        ) dt
       GROUP BY 1,2
      ) dt
     GROUP BY 1,2
    ) dt
   GROUP BY 1
 ) dt
GROUP BY 1

这篇关于时间戳汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

时间戳汇总 [英] Aggregation by timestamp

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

时间戳汇总 [英] Aggregation by timestamp

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭