gpt4 book ai didi

MySQL:用 FROM 子句中的相关子查询重写 MSSQL?

转载 作者:行者123 更新时间:2023-11-30 21:23:10 25 4
gpt4 key购买 nike

我们有一个包含网站页面浏览量的表格,例如:

time      | page_id
----------|-----------------------------
1256645862| pageA
1256645889| pageB
1256647199| pageA
1256647198| pageA
1256647300| pageB
1257863235| pageA
1257863236| pageC

在我们的生产表中,目前大约有 40K 行。我们想要为每一天生成在过去 30 天、60 天和 90 天内查看的独特页面的计数。因此,在结果集中,我们可以查找一天,并查看在该天之前的 60 天内访问了多少唯一页面。

我们能够让查询在 MSSQL 中运行:

SELECT DISTINCT
CONVERT(VARCHAR,P.NDATE,101) AS 'DATE',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE NDATE BETWEEN DATEADD(D,-29,P.NDATE) AND P.NDATE) AS SUB) AS '30D',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE NDATE BETWEEN DATEADD(D,-59,P.NDATE) AND P.NDATE) AS SUB) AS '60D',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE NDATE BETWEEN DATEADD(D,-89,P.NDATE) AND P.NDATE) AS SUB) AS '90D'
FROM PERFLOG P
ORDER BY 'DATE'

注意:因为MSSQL没有FROM_UNIXTIME函数,所以我们添加了NDATE列来测试,它就是转换后的时间。生产表中不存在 NDATE。

将此查询转换为 MySQL 会出现“Unknown colum P.time”错误:

SELECT DISTINCT
FROM_UNIXTIME(P.time,'%Y-%m-%d') AS 'DATE',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE FROM_UNIXTIME(time,'%Y-%m-%d') BETWEEN DATE_SUB(FROM_UNIXTIME(P.time,'%Y-%m-%d'), INTERVAL 30 DAY) AND FROM_UNIXTIME(P.time,'%Y-%m-%d')) AS SUB) AS '30D',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE FROM_UNIXTIME(time,'%Y-%m-%d') BETWEEN DATE_SUB(FROM_UNIXTIME(P.time,'%Y-%m-%d'), INTERVAL 60 DAY) AND FROM_UNIXTIME(P.time,'%Y-%m-%d')) AS SUB) AS '60D',
(SELECT COUNT(DISTINCT SUB.PAGE_ID) FROM (SELECT PAGE_ID FROM perflog WHERE FROM_UNIXTIME(time,'%Y-%m-%d') BETWEEN DATE_SUB(FROM_UNIXTIME(P.time,'%Y-%m-%d'), INTERVAL 90 DAY) AND FROM_UNIXTIME(P.time,'%Y-%m-%d')) AS SUB) AS '90D'
FROM PERFLOG P
ORDER BY 'DATE'

我理解这是因为我们不能在外部 FROM 子句中有引用表的相关子查询。但是,不幸的是,我们不知道如何将此查询转换为在 MySQL 中工作。现在,我们只需从表中返回所有 DISTINCT 行并在 PHP 中对其进行后处理。 40K 行大约需要 2-3 秒。当我们有 100 行或 1000 行时,我很担心性能。

可以在MySQL中做吗?如果是这样,我们能否期望它比我们的 PHP 后处理解决方案执行得更好。

更新:这是创建表的查询:

CREATE TABLE  `perflog` (
`user_id` VARBINARY( 40 ) NOT NULL ,
`elapsed` float UNSIGNED NOT NULL ,
`page_id` VARCHAR( 255 ) NOT NULL ,
`time` INT( 10 ) UNSIGNED NOT NULL ,
`ip` VARBINARY( 40 ) NOT NULL ,
`agent` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `user_id` , `page_id` , `time` , `ip`, `agent` )
) ENGINE MyISAM

到目前为止,我们的生产表有大约 4 万行!

最佳答案

注意:我是在阅读@astander、@Donnie、@longneck 的解决方案后写下这篇文章的。

我知道性能很重要,但为什么不存储聚合?十年的每一行一天是 3650 行,每行只有几列。

TABLE dimDate (DateKey int (PK), Year int, Day int, DayOfWeek varchar(10), DayInEpoch....)
TABLE AggVisits (DateKey int (PK,FK), Today int, Last30 int, Last60 int, Last90 int)

这样您就可以在一天结束时仅运行一次查询,仅运行一天。预先计算的聚合是任何高性能分析解决方案(多维数据集)的基础。

更新:
您可以通过引入另一列 DayInEpoch int(自 1990-01-01 以来的天数)来加快这些查询的速度。然后您可以删除所有那些日期/时间转换函数。

关于MySQL:用 FROM 子句中的相关子查询重写 MSSQL?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1771373/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com