通过比较一个事件的多个日期和其他事件的最小日期来进行大查询标准sql过滤

hm2xizp9 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(584)

我已经创建了一个查询，其中有设备标识符，包括min（first\u login\u datetime）、min（first\u purchase\u datetime）和push\u datetime列表（每个设备可能接收到多个push）。单个设备见下表示例。
我正在尝试筛选出在第一个登录日期时间和第一个购买日期时间之间接收到推送的设备。所讨论的设备（请参阅下表中的设备a）在第一个登录日期时间和第一个购买日期时间之间以及第一个购买日期时间之后都接收到推送。因此，我无法为“其中push\u datetime不在first\u login\u datetime和first\u purchase\u datetime之间”添加筛选条件，因为这将删除介于两者之间的push\u datetime示例，但设备\u a仍保留在我的队列中，因为在first\u purchase\u datetime之后接收到推送，即使设备在两个日期时间之间仍然有一个推送。我需要能够从查询中完全删除这个设备（以及任何在两个日期时间之间有推送的设备），有什么建议吗？

device_id    first_login_datetime first_purchase_datetime push_datetime
1   device_a    2020-06-27T00:26:10 2020-07-02T19:35:05 2020-06-28T20:00:14.187559
2   device_a    2020-06-27T00:26:10 2020-07-02T19:35:05 2020-07-01T20:07:13.020859
3   device_a    2020-06-27T00:26:10 2020-07-02T19:35:05 2020-07-01T23:52:33.943492
4   device_a    2020-06-27T00:26:10 2020-07-02T19:35:05 2020-07-03T16:10:09.884610
5   device_a    2020-06-27T00:26:10 2020-07-02T19:35:05 2020-07-03T19:46:51.505925

sql google-bigquery

来源：https://stackoverflow.com/questions/63060988/big-query-standard-sql-filtering-by-comparing-multiple-dates-for-one-event-to-mi

2条答案

按热度按时间

rn0zuynd1#

您需要获取要删除的不同设备，然后对原始表进行左联接。

with my_table as (select * from `project.dataset.table`),
devices_to_remove as (
  select distinct device_id
  from my_table
  where push_datetime between first_login_datetime and first_purchase_datetime
)
select my_table.* 
from my_table 
left join devices_to_remove using(device_id)
where devices_to_remove.device_id is null

这将从任何符合排除条件的设备中删除所有记录。

赞(0）回复(0）举报 2021-07-24

gopyfrb32#

我认为窗口函数是最简单（也是最快）的解决方案：

select t.* except (bad_cnt)
from (select t.*,
             countif(push_datetime between first_login_datetime and first_purchase_datetime) over (partition by device_id) as bad_cnt
      from t
     ) t
where bad_cnt > 0

赞(0）回复(0）举报 2021-07-24

我来回答

通过比较一个事件的多个日期和其他事件的最小日期来进行大查询标准sql过滤

2条答案

相关问题

热门标签

最新问答