时间戳转换器

MySQL 查询以查找每周唯一新访问者的数量

日期:2023-05-09     浏览:275    
【中文标题】MySQL 查询以查找每周唯一新访问者的数量【英文标题】:MySQL query to find number of unique new visitors per week 【发布时间】:2016-02-26 19:50:31 【问题描述】:

我在 MySQL 中有 3 个表: 1)页面(ID,标题) 2) 访客(身份证、姓名) 3)page_visit(page_id、visitor_id、timestamp_of_visit)

访问者可以在几天内多次访问页面。因此,虽然我们将有一行用于页面,而将一行用于访问者,但我们可以有多个 page_visit 行,每行都有访问的时间戳。

我正在尝试按周查找唯一身份访问者的数量。我知道如何获取非唯一的“按周计数”查询(即“我每周看到多少访问者”)。不过,我不确定如何按周挑选唯一访问者,因为访问者仅在他们第一次出现时才会出现在列表中。

-----------    -----------    ----------------------------
| page    |    | visitor |    | page_visit               |
-----------    -----------    ----------------------------
|id |title|    |id |name |    |pid|vid|timestamp of visit|
-----------    -----------    ----------------------------
| 1 | p1  |    | 1 | v1  |    | 1 | 1 | 02-18-2016:08:30 |
| 2 | p2  |    | 2 | v2  |    | 1 | 1 | 02-18-2016:10:00 |
| 3 | p3  |    | 3 | v3  |    | 1 | 3 | 02-20-2016:23:45 |
| 4 | p4  |    | 4 | v4  |    | 2 | 3 | 02-22-2016:07:30 |
| 5 | p5  |    | 5 | v5  |    | 3 | 1 | 02-23-2016:08:30 |
| 6 | p6  |    | 6 | v6  |    | 3 | 6 | 02-24-2016:09:30 |

结果集应该显示什么:

------------------------
| results              |
------------------------
| Week of    | Net new |
------------------------
| 02-15-2016 | 2       |
| 02-22-2016 | 1       | 

如前所述,我可以弄清楚如何按周显示所有访问者。我不确定如何获得唯一身份访问者。

我尝试做一个 min(timestamp of visit),但是根据我尝试的位置,它返回了所有行中的最低时间戳(可以理解...)。

任何帮助将不胜感激!

【问题讨论】:

【参考方案1】:

当您第一次遇到它时,这是一个棘手的问题。它需要两个级别的聚合。第一个获得每个访问者的第一次访问,第二个按时间汇总。下面按天做总结:

select date(minvd), count(*) as numvisitors
from (select vid, min(visitdate) as minvd
      from page_visit pv
      group by vid
     ) v
group by date(minvd)
order by date(minvd);

转换为周总是有点棘手——它们是从星期一开始的吗?周六结束?在周五? (我已经看到了所有这些。)但是,以上是累加的,因此您只需将给定一周的所有值相加即可获得您的值。

【讨论】:

谢谢!!!这非常有帮助,我可以根据这个来工作!我一直在试图弄清楚如何排列元素,这样我就可以运行一个计数函数。对于“周”计算,我使用了以下格式: DATE_FORMAT(DATE_ADD(v.minvd, INTERVAL(1-DAYOFWEEK(v.minvd)) DAY),'%Y-%m-%d') AS 'Week of ' 最后,一个 'GROUP BY YEARWEEK(v.minvd)' 我的 MySQL 引擎默认从星期日开始一周。谢谢林诺夫先生!【参考方案2】:

如果您想在没有子查询的情况下执行此操作:

SELECT
    <week>,
    COUNT(DISTINCT PV.vid)
FROM
    Page_Visit PV
LEFT OUTER JOIN Page_Visit PV2 ON
    PV2.vid = PV.vid AND
    PV2.visit_date < PV.visit_date
WHERE
    PV2.vid IS NULL
GROUP BY
    <week>

正如 Gordon 所提到的,如何确定一周可能会很棘手。只需在您看到&lt;week&gt; 的位置添加该计算。就个人而言,我喜欢使用日历表来实现这种功能,但这取决于你。您可以直接针对PV.visit_date 运行任何表达式来确定它。

【讨论】:

相关文章

帮助查找此 MySQL 页面中的安全漏洞?

{...的班级的作业。通过文本框,他们可以访问数据库以提交查询并查看它是否返回正确的数据集。我想看看他们是否有什么恶意。这是SHOWGRANTS查询的结果:Grantsforuser@localh}

用于查找每个给定位置的最多供应商数量的 SQL 查询

{】用于查找每个给定位置的最多供应商数量的SQL查询【英文标题】:SQLquerytofindthemostnumberofvendorsperagivenlocation【发布时间】:2020-12-2119:55:27【问题描述】:您能帮我找到正确的MySQL查询,以获取每个给定位置的最多供应商数量,...}

如何改进热图的大型数据集的 KQL 查询

{】如何改进热图的大型数据集的KQL查询【英文标题】:HowcanIimproveKQLqueryforlargedatasetforheatmap【发布时间】:2021-09-1422:03:37【问题描述】:我在下面有一个KQL查询,它将提供一个非常好的热图,以按国家/地区绘制AzureWAF的最高访问...}

MySQL权限表,从PHP访问以在另一个数据库中使用?

{...MySQL的数据库相关联的PHP应用程序。为了在我的数据库上查找用户(或用户组)权限,应用程序是否需要连接到“mysql”数据库才能访问其用户和数据库表,或}

在python中查找列表的子集的总和

{...东西......我有一长串整数,在这种情况下代表网站的每日访问者。我想要一个每周访问者的新列表。所以我需要从原始列表中获取七组,将它们相加,然后将它们添加到新列表中。我的解决方案似乎很暴力,不优雅:numweeks=len(da...}

为访问查询创建自定义聚合函数

{】为访问查询创建自定义聚合函数【英文标题】:CreatingaCustomAggregateFunctionforAccessQueries【发布时间】:2018-07-2003:09:24【问题描述】:最常见/频繁/密集值我想查找(或创建自定义)聚合函数以用于MSAccess查询,它将返回最常见的...}

MySQL:空间查询以查找纬度/经度点是不是位于给定边界内

{】MySQL:空间查询以查找纬度/经度点是不是位于给定边界内【英文标题】:MySQL:SpatialQuerytofindwhetheralatitude/longitudepointislocatedwithinagivenboundaryMySQL:空间查询以查找纬度/经度点是否位于给定边界内【发布时间】:2014-08-2518:11:57【问...}

MySql 查询以查找给定日期“从”和“到”可用的房间

{】MySql查询以查找给定日期“从”和“到”可用的房间【英文标题】:MySqlquerytofindroomsavailablegivendates\'From\'and\'to\'【发布时间】:2018-11-1506:46:13【问题描述】:表格如下rooms(RoomId(PK)(int),RoomName(varchar))bookings(RoomId(FK)(int),From_D(date),T...}

帮助形成 mysql 查询以查找给定日期范围的免费(可用)场所/资源

{】帮助形成mysql查询以查找给定日期范围的免费(可用)场所/资源【英文标题】:helpinformingmysqlquerytofindfree(available)venues/resourcesforagivedaterange【发布时间】:2011-01-0405:42:19【问题描述】:我有这样的表格和数据:场地表包含:id+-...}

pythondbutils数据库连接池(代码片段)

{...、连接池的作用Python可以使用MySQLdb进行数据库的连接及查询/插入/更新等操作,但是每次连接MySQL数据库请求时,都是独立的去请求访问,相当浪费资源而且访问数量达到一定数量时,对mysql的性能会产生较大的影...}

Copyright ©2021 时间戳转换器 短句 114pp | 陕ICP备18005036号