mysql - 将布尔值设置为 mysql 记录指示在选择的时间戳范围之前是否存在其他记录
问题描述
基本表类似于:
mysqlTable:
visitorID ,park ,DateTimeStamp
8369 ,Birmingham ,12/27/2018 03:26:38 PM
8369 ,Birmingham ,12/28/2018 11:27:32 AM
8828 ,Central ,01/02/2019 10:01
8828 ,Central ,01/04/2019 9:50
8825 ,Central ,12/21/2018 09:47:27 AM
8821 ,Central ,12/26/2018 10:11:40 AM
8821 ,Central ,02/03/2019 10:00:59 AM
8821 ,Central ,01/02/2019 10:04
88281 ,Central ,01/04/2019 9:53
从这张表中,我正在创建一个新表,在其中我正在计算特定公园中的访客 ID,然后按访客 ID 和特定时间段内的访问日期对访问进行分组。
mysql query:
SELECT COUNT(*)AS visits,dateTimeStamp,visitorID
FROM parkVisits
WHERE
dateTimeStamp BETWEEN '2019-01-01 00:00:01' AND '2019-01-04 23:59:59'
AND park ='Central'
GROUP BY visitorID, CAST(dateTimeStamp AS DATE);
我的结果:
mysql table:
visits ,dateTimeStamp ,visitorID
2 ,01/02/2019 10:01 ,8828
1 ,01/02/2019 10:04 ,8821
1 ,01/04/2019 9:53 ,88281
我想要一列,其中布尔值指示访问者是否在其行的指定日期之前的任何时间访问过。 我正在考虑将桌子上最早的 dateTimeStamp 与最早的给定日期进行比较,但是可能会出现第一次访问和回访发生在给定时间段内的情况。
预期的:
mysql table:
visits ,dateTimeStamp ,visitorID ,returningVisitor
2 ,01/02/2019 10:01, 8828 ,TRUE
1 ,01/02/2019 10:04, 8821 ,FALSE
1 ,01/04/2019 9:53, 88281 ,FALSE
编辑:
我正在使用 MySQL 5.6.40
解决方案
假设您正在运行 MySQL 8.0,您可以使用带有窗口函数的内部查询FIRST_VALUE
来获取分析期间第一次访问的时间戳。然后在外部查询中,EXISTS
可以使用带有子查询的子句来检查当前访问者之前是否曾经访问过同一个公园。
SELECT
x.visits,
x.dateTimeStamp,
x.visitorID,
EXISTS (
SELECT 1 FROM parkVisits WHERE park = x.park AND visitorID = x.visitorID AND dateTimeStamp < x.dateTimeStamp
) returningVisitor
FROM (
SELECT DISTINCT
COUNT(*) OVER (PARTITION BY p.visitorID) visits,
FIRST_VALUE(p.dateTimeStamp) OVER (PARTITION BY p.visitorID ORDER BY p.dateTimeStamp) dateTimeStamp,
p.visitorID,
p.park
FROM parkVisits p
WHERE
p.dateTimeStamp BETWEEN '2019-01-01 00:00:01' AND '2019-01-04 23:59:59'
AND p.park ='Central'
) x
ORDER BY 1 desc, 2
在这个数据库中处理您的示例数据,它返回:
| visits | dateTimeStamp | visitorID | returningVisitor |
| ------ | ------------------- | --------- | ---------------- |
| 2 | 2019-01-02 10:01:00 | 8828 | 0 |
| 1 | 2019-01-02 10:04:00 | 8821 | 1 |
| 1 | 2019-01-04 09:53:00 | 88281 | 0 |
注意:我认为回访者是8821
,而不是8828
您的问题中显示的。
如果您运行的是不支持窗口函数的较低版本的 MySQL,您可以GROUP BY
在子查询中使用子句,例如:
SELECT
x.visits,
x.dateTimeStamp,
x.visitorID,
EXISTS (
SELECT 1 FROM parkVisits WHERE park = x.park AND visitorID = x.visitorID AND dateTimeStamp < x.dateTimeStamp
) returningVisitor
FROM (
SELECT DISTINCT
COUNT(*) visits,
MIN(p.dateTimeStamp) dateTimeStamp,
p.visitorID,
p.park
FROM parkVisits p
WHERE
p.dateTimeStamp BETWEEN '2019-01-01 00:00:01' AND '2019-01-04 23:59:59'
AND p.park ='Central'
GROUP BY p.visitorID, p.park
) x
ORDER BY 1 desc, 2
看到这个 db fiddle。
推荐阅读
- angular - 没有页眉/面包屑/页脚的登录页面
- c# - 检查 json 是否真的是 T 类型
- python - Matplotlib/numpy/pandas IndexError:索引 0 超出轴 0 的范围,大小为 0
- php - 使用 CSS 更改 PHP 中的背景颜色
- apache-spark - map() 和 flatMap() 的输出 - 有什么区别
- vue.js - 使用vue中的复选框过滤列表项
- java - Java 类使用反射扩展另一个类 (bukkit)
- reactjs - 将状态值从 redux reducer 传递到另一个 reducer
- php - 如何在我的控制器索引函数中放置“分页”方法?
- c++ - 为什么在将一个对象复制到另一个对象时,C++ 需要对两个对象进行低级 const 限定?