sql - 在SQL(Hive)中使用collect_list函数汇总用户序列-6ren

sql - 在SQL(Hive)中使用collect_list函数汇总用户序列

转载作者：行者123 更新时间：2023-12-02 20:47:26

我有一个数据集，可以代表个人的使用方式，即他们必须输入值的形式。

表格可以按照“姓氏”以外的任何顺序完成，步骤1是。

时间戳用于暗示表单完成的顺序。

例如-用户12345678在2017-10-25的04:58:08开始填写表格，并依次完成表格。

用户12345679在同一天的05:00:02开始填写表单，但仅涉及步骤2

用户12345680在05:05:06开始填写表单，但分散了注意力，没有超出步骤1，但在完成之前又返回了两次

用户12345681从06:31:12开始填写表单，并从第1步开始，但随后随机填写该表单。

|    date  |     timestamp      | user_id  |  visit_id   |      event    |  event_seq  |
|--------------------------------------------------------------------------------------|
|2017-10-25| 2017-10-25 04:58:08| 12345678 |    1234     |   firstname   |       1     |
|2017-10-25| 2017-10-25 04:58:20| 12345678 |    1234     |   lastname    |       2     |
|2017-10-25| 2017-10-25 04:58:35| 12345678 |    1234     |      dob      |       3     |
|2017-10-25| 2017-10-25 04:58:40| 12345678 |    1234     |   postcode    |       4     |
|2017-10-25| 2017-10-25 04:58:40| 12345678 |    1234     |     email     |       5     |
|2017-10-25| 2017-10-25 05:00:02| 12345679 |    1235     |   firstname   |       1     |
|2017-10-25| 2017-10-25 05:00:10| 12345679 |    1235     |   lastname    |       2     |
|2017-10-25| 2017-10-25 05:05:06| 12345680 |    1236     |   firstname   |       1     |
|2017-10-25| 2017-10-25 05:30:24| 12345680 |    1236     |   firstname   |       1     |
|2017-10-25| 2017-10-25 06:17:24| 12345680 |    1236     |   firstname   |       1     |
|2017-10-25| 2017-10-25 06:20:30| 12345680 |    1236     |   lastname    |       2     |
|2017-10-25| 2017-10-25 06:20:45| 12345680 |    1236     |      dob      |       3     |
|2017-10-25| 2017-10-25 06:20:45| 12345680 |    1236     |   postcode    |       4     |
|2017-10-25| 2017-10-25 06:20:45| 12345680 |    1236     |     email     |       5     |
|2017-10-25| 2017-10-25 06:31:12| 12345681 |    1237     |   firstname   |       1     |
|2017-10-25| 2017-10-25 06:31:18| 12345681 |    1237     |     email     |       5     |
|2017-10-25| 2017-10-25 06:31:50| 12345681 |    1237     |   lastname    |       2     |
|2017-10-25| 2017-10-25 06:32:16| 12345681 |    1237     |   postcode    |       4     |
|2017-10-25| 2017-10-25 06:32:40| 12345681 |    1237     |      dob      |       3     |

我编写的代码如下，并引用了一个预先存在的表，其中CASE WHEN用于在名为“事件”的变量中为表单的每个步骤分配一个数字:

SELECT date
,time_stamp
,user_id
,visit_id
,collect_list(events) as event_seq
FROM my_table
GROUP BY date
,start_time
time_stamp
,user_id
,visit_id

不出所料，这似乎将所有用户12345680的交互都列出在一个字符串中。

|    date  |     timestamp      | user_id  |  visit_id   |  event_seq  |
|----------------------------------------------------------------------|
|2017-10-25| 2017-10-25 04:58:08| 12345678 |    1234     |  1,2,3,4,5  |
|2017-10-25| 2017-10-25 05:00:02| 12345679 |    1235     |     1,2     |
|2017-10-25| 2017-10-25 05:05:06| 12345680 |    1236     |1,1,1,2,3,4,5| 
|2017-10-25| 2017-10-25 06:31:12| 12345681 |    1237     |  1,5,2,4,3, |

但是，我希望看到的是序列中第一个事件标记的每一行，类似于下面的结果集，其中每个用户12345680的重新启动都发生在不同的行上。

|    date  |     timestamp      | user_id  |  visit_id   |  event_seq  |
|----------------------------------------------------------------------|
|2017-10-25| 2017-10-25 04:58:08| 12345678 |    1234     |  1,2,3,4,5  |
|2017-10-25| 2017-10-25 05:00:02| 12345679 |    1235     |     1,2     |
|2017-10-25| 2017-10-25 05:05:06| 12345680 |    1236     |      1      |
|2017-10-25| 2017-10-25 05:30:24| 12345680 |    1236     |      1      |
|2017-10-25| 2017-10-25 06:17:24| 12345680 |    1236     |  1,2,3,4,5  |      
|2017-10-25| 2017-10-25 06:31:12| 12345681 |    1237     |  1,5,2,4,3, |

有人可以提供有关如何使用collect_list达到所需结果集的任何指导吗？

最佳答案

在您的SQL中，似乎在表中有一列名为start_time的列。假设您有一个，请参见下面的解决方案

表

CREATE EXTERNAL TABLE my_table(
  event_date DATE,
  event_start_timestamp TIMESTAMP,
  event_timestamp TIMESTAMP,
  user_id STRING,
  visit_id STRING,
  event STRING,
  event_seq STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE;

数据

2017-10-25| 2017-10-25 04:58:08| 2017-10-25 04:58:08| 12345678 |    1234     |   firstname   |       1     
2017-10-25| 2017-10-25 04:58:08| 2017-10-25 04:58:20| 12345678 |    1234     |   lastname    |       2     
2017-10-25| 2017-10-25 04:58:08| 2017-10-25 04:58:35| 12345678 |    1234     |      dob      |       3     
2017-10-25| 2017-10-25 04:58:08| 2017-10-25 04:58:40| 12345678 |    1234     |   postcode    |       4     
2017-10-25| 2017-10-25 04:58:08| 2017-10-25 04:58:40| 12345678 |    1234     |     email     |       5     
2017-10-25| 2017-10-25 05:00:02| 2017-10-25 05:00:02| 12345679 |    1235     |   firstname   |       1     
2017-10-25| 2017-10-25 05:00:02| 2017-10-25 05:00:10| 12345679 |    1235     |   lastname    |       2     
2017-10-25| 2017-10-25 05:05:06| 2017-10-25 05:05:06| 12345680 |    1236     |   firstname   |       1     
2017-10-25| 2017-10-25 05:30:24| 2017-10-25 05:30:24| 12345680 |    1236     |   firstname   |       1     
2017-10-25| 2017-10-25 06:17:24| 2017-10-25 06:17:24| 12345680 |    1236     |   firstname   |       1     
2017-10-25| 2017-10-25 06:17:24| 2017-10-25 06:20:30| 12345680 |    1236     |   lastname    |       2     
2017-10-25| 2017-10-25 06:17:24| 2017-10-25 06:20:45| 12345680 |    1236     |      dob      |       3     
2017-10-25| 2017-10-25 06:17:24| 2017-10-25 06:20:45| 12345680 |    1236     |   postcode    |       4     
2017-10-25| 2017-10-25 06:17:24| 2017-10-25 06:20:45| 12345680 |    1236     |     email     |       5     
2017-10-25| 2017-10-25 06:31:12| 2017-10-25 06:31:12| 12345681 |    1237     |   firstname   |       1     
2017-10-25| 2017-10-25 06:31:12| 2017-10-25 06:31:18| 12345681 |    1237     |     email     |       5     
2017-10-25| 2017-10-25 06:31:12| 2017-10-25 06:31:50| 12345681 |    1237     |   lastname    |       2     
2017-10-25| 2017-10-25 06:31:12| 2017-10-25 06:32:16| 12345681 |    1237     |   postcode    |       4     
2017-10-25| 2017-10-25 06:31:12| 2017-10-25 06:32:40| 12345681 |    1237     |      dob      |       3

SQL查询

SELECT event_date,
       user_id, 
       visit_id, 
       event_start_timestamp, 
       collect_list(event_seq)
  FROM (SELECT event_date, 
               event_start_timestamp, 
               event_timestamp, 
               user_id, 
               visit_id, 
               event_seq 
          FROM my_table 
          SORT BY user_id, visit_id, event_start_timestamp, event_timestamp ASC) v
 GROUP BY event_date, user_id, visit_id, event_start_timestamp ;

输出

2017-10-25   12345678       1234        2017-10-25 04:58:08 ["       1     ","       2     ","       3     ","       4     ","       5     "]
2017-10-25   12345679       1235        2017-10-25 05:00:02 ["       1     ","       2     "]
2017-10-25   12345680       1236        2017-10-25 05:05:06 ["       1     "]
2017-10-25   12345680       1236        2017-10-25 05:30:24 ["       1     "]
2017-10-25   12345680       1236        2017-10-25 06:17:24 ["       1     ","       2     ","       3     ","       4     ","       5     "]
2017-10-25   12345681       1237        2017-10-25 06:31:12 ["       1     ","       5     ","       2     ","       4     ","       3     "]

让我们知道这是否有效!

并且请不要使用列名作为DATE，TIMESTAMP等，它们是保留字:)

关于sql - 在SQL(Hive)中使用collect_list函数汇总用户序列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47398295/

文章推荐： ubuntu - docker:无法连接到 Docker 守护进程

文章推荐： Azure 容器注册表获取凭据时出错

文章推荐： python - pytest KeyError:docker容器内的 'USER'

Python:过滤器(函数，序列)和映射(函数，序列)之间的区别
我正在阅读 Python 文档以真正深入了解 Python 语言，并遇到了 filter 和 map 函数。我以前使用过过滤器，但从未使用过映射，尽管我在 SO 上的各种 Python 问题中都见过这
algorithm - 给定一个 preOrder 和 inOrder 序列，可能有多少级阶 BST 序列？
当我尝试打印 BST 的级别顺序时，这个问题提示了我。这是一个 Pre-Order Sequence: 4, 1, 2, 3, 5, 6, 7, 8 In_order Sequence : 1, 2
c++ - 定义函数后 main 出错？ "undefined reference to ' 序列::序列()'"
我的代码在 main(序列测试；)的第一行出现错误，指出它是对 sequence::sequence() 的 undefined reference 。我无法更改 main 中的代码。有谁知道我该如何
latex 序列\/?
这可能很简单，但我在通常的 latex 指南中找不到任何相关内容。在这句话中: {\em hello\/} “\/”的目的是什么？最佳答案这就是所谓的斜体校正。其目的是确保斜体文本后有适当的间距。
Postgresql 序列
当我从 Postgresql 表中删除所有记录，然后尝试重置序列以在插入时开始一个编号为 1 的新记录时，我得到不同的结果: SELECT setval('tblname_id_seq', (SELE
30、MariaDB 序列
在版本10.0.3中，MariaDB引入了一种称为序列的存储引擎。其ad hoc为操作生成整数序列，然后终止。该序列包含正整数，以降序或升序排列，并使用起始，结束和递增值。它不允许在多个查询中
数字的 Groovy 序列
如何在 Groovy 中获取给定数字的序列，例如: def number = 169 // need a method in groovy to find the consecutive number
作为特定复杂类型的扩展的任何类型元素的 xsd 序列
基本上，如果这是 .NET，它看起来像这样: ISomething { string A { get; } int B { get; } } var somethings = new List
非阻塞赋值的 Verilog 序列
说以下代码部分(同一块): A <= 1 A <= 2 变量 A 总是被赋值为 2 吗？还是会出现竞争条件并分配 1 或 2？我对非阻塞赋值的理解是，由硬件在 future 分配变量 A，因此它可能
WiX Action 序列
在运行 WiX 设置时，我正在寻找操作列表及其顺序。不知何故，官方网站似乎没有提供任何信息。基本问题是我想正确安排我的自定义操作。通常我需要使用 regsvr32.exe 注册一个 DLL，而这只能
具有至少一个元素的 F# 序列
F#初学者在这里我想创建一个类型，它是具有至少一个元素的另一种具体类型(事件)的序列。任何其他元素都可以在以后随时添加。通常在 C# 中，我会创建一个具有私有(private) List 和公共(p
sql - 在Oracle中删除所有用户表/序列
作为构建过程和不断发展的数据库的一部分，我试图创建一个脚本，该脚本将删除用户的所有表和序列。我不想重新创建用户，因为这将需要比所允许的更多的权限。我的脚本创建了一个过程来删除表/序列，执行该过程，然
日期和向量的 R 序列
我想恢复两个向量的第一个日期和相同向量的第二个日期之间的日期序列，.... 这是一个例子: dates1 = as.Date(c('2015-10-01', '2015-03-27', '2015-0
SQL ORDER BY(序列)
这个问题已经有答案了: sql ORDER BY multiple values in specific order? (12 个回答) 已关闭 9 年前。我有一个 sql 语句，我想要ORDER
日期和向量的 R 序列
我想恢复两个向量的第一个日期和相同向量的第二个日期之间的日期序列，.... 这是一个例子: dates1 = as.Date(c('2015-10-01', '2015-03-27', '2015-0
java - 如何在java中转义],[序列？
在用java编写代码时，我需要用“],[”分割字符串。下面是我的代码。 try (BufferedReader reader = new BufferedReader(new InputStreamR
数字的 Collatz 序列
这个问题已经有答案了: Project Euler Question 14 (Collatz Problem) (8 个回答) 已关闭 9 年前。我正在尝试查找数字的 Collatz 序列。以下
C++:使用循环和变量模式(序列)
我有一个例程函数process_letter_location(const char& c, string &word)。在我的 main 中，我声明了一系列字符串变量，如下所示: string s
c++ - 最长的多米诺骨牌链/序列
我需要找到最长的多米诺骨牌链，给定一组 12 个随机挑选的多米诺骨牌。我已经递归地生成了多米诺骨牌的所有可能性(使用 0 到 12 的面值有 91 种可能性)。多米诺骨牌由一 block “砖 blo
c++ - 序列 vector
我有这个数据结构 Seq，它继承了类 vector 但有一些额外的功能。使用这个数据结构 Seq 我有这个预定义的数据结构: typedef Seq > MxInt2d; 我现在想要一个包含多个 Mx

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 在SQL(Hive)中使用collect_list函数汇总用户序列