时间戳

首页 > 解决方案 > SQL：如何根据边界条件选择某些值？

sql - SQL：如何根据边界条件选择某些值？

问题描述

我正在尝试制定一个 SQL 来根据边界条件从列中选择特定值。（下面的 DDL）。

以下是预期的输出：

在此处输入图像描述

说明： 对于具有 Session_id ( A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899) 的第一行，操作类型将为 None，因为它既不是，也不selectSearchHistory是selectISS第 2 行和第 3 行。

但是对于第 2 行，我有 action_type selectSearchHistory，因此我拉入该值，其余保持不变。对于第 3 行，我有 selectISS，因此我提取了该值，其余保持不变

这是我到目前为止所做的：

select session_id, 
lag(action_type) over (partition by cust_id, device_event_date order by session_id) as prev_action_type,
tbl_name, marketplace_id, enc_customer_id, device_event_date, page_type, action_type,
ROW_NUMBER () over (partition by session_id order by session_id ) as rn
from test_table where action_type is NOT NULL;

如图所示，我无法将它们分组到每个 action_type 行中。

create table test_table (session_id varchar(200), table_name varchar(100), page_type varchar(100), action_type varchar(100), territory varchar(100), cust_id varchar(100), device_event_date timestamp);


INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','page_view','browseFind','','1','A1XYZB0','44270.6986909606');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','page_view','browseSearch','','1','A1XYZB0','44270.6987389699');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','clicks','browseSearch','initiateSearch','1','A1XYZB0','44270.6987484028');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','clicks','browseSearch','executeSearch','1','A1XYZB0','44270.6988179745');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','page_view','browseSearch','','1','A1XYZB0','44270.698827581');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','clicks','searchResults','playSong','1','A1XYZB0','44270.6989970139');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:46:06.899','plbk_attr_search','searchResults','','1','A1XYZB0','44270.6989970139');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','page_view','browseFind','','1','A1XYZB0','44270.7023368287');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','page_view','browseSearch','','1','A1XYZB0','44270.7023457292');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','clicks','browseSearch','selectSearchHistory','1','A1XYZB0','44270.7025611227');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','clicks','browseSearch','executeSearch','1','A1XYZB0','44270.7025611227');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','page_view','browseSearch','','1','A1XYZB0','44270.7025758102');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','clicks','searchResults','selectAlbum','1','A1XYZB0','44270.7026314236');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:51:21.902','plbk_attr_search','searchResults','','1','A1XYZB0','44270.7026314236');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','page_view','browseFind','','1','A1XYZB0','44270.7054355556');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','page_view','browseSearch','','1','A1XYZB0','44270.7054554398');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','clicks','browseSearch','initiateSearch','1','A1XYZB0','44270.7054703241');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','clicks','browseSearch','selectISS','1','A1XYZB0','44270.7055178704');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','clicks','browseSearch','executeSearch','1','A1XYZB0','44270.7055178704');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','page_view','browseSearch','','1','A1XYZB0','44270.7055278241');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','clicks','searchResults','playSong','1','A1XYZB0','44270.7055589236');
INSERT INTO test_table VALUES('A1XYZB0-A2825NDLA7WDZV-2021-03-15 16:55:49.632','plbk_attr_search','searchResults','','1','A1XYZB0','44270.7055589236');

device_event_time 时间戳是纪元。

标签： sqlamazon-redshift

解决方案

不完全确定您的数据或要求，但这是在您的示例中产生输出的 CTE 方法......

WITH ssh AS
(
    SELECT session_id, action_type 
    FROM test_table 
    WHERE action_type = 'selectSearchHistory'
), siss AS
(
    SELECT session_id, action_type 
    FROM test_table 
    WHERE action_type = 'selectISS'
), s AS
(
    SELECT session_id, territory, cust_id 
    FROM test_table
    GROUP BY session_id, territory, cust_id  
)
SELECT 
    s.session_id,
    COALESCE(ssh.action_type, COALESCE(siss.action_type, 'None')) AS action_type,
    s.territory,
    s.cust_id
FROM s
LEFT JOIN ssh ON ssh.session_id = s.session_id
LEFT JOIN siss ON siss.session_id = s.session_id;

单击此处查看它的实际效果并在 SQL Fiddle 中玩一玩

推荐阅读