我有一个名为"purchases"的MySQL DB表,其中填充了各种事务的记录。我想知道在一周中的某一天发生了多少%的事务,我想知道如何使用SQL来实现这一点。我在这个表中有一个名为"提交"的字段,它保存了记录事务的标准unix时间戳。
到目前为止我所拥有的:
SELECT COUNT(DISTINCT DATE_FORMAT(submitted,%a)) FROM purchases;
我的最终目标是创建一个表,其中包含给定日期(周一至周日)所有事务的%。
我的表格结构如下:
CREATE TABLE IF NOT EXISTS `purchases` (<br />
`id` int(11) NOT NULL AUTO_INCREMENT,<br />
`orderinfo` varchar(50) NOT NULL,<br />
`amount` varchar(5) NOT NULL,<br />
`reference` varchar(15) NOT NULL,<br />
`name` varchar(50) NOT NULL,<br />
`address` varchar(100) NOT NULL,
`town` varchar(50) NOT NULL,<br />
`postcode` varchar(12) NOT NULL,<br />
`submitted` int(11) NOT NULL,<br />
UNIQUE KEY `id` (`id`)<br />
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
将时间戳转换为日期,然后获取COUNT,这将获取计数以及百分比
SELECT date ( from_unixtime(submitted) ), count(*) as `count`, count(*)/(select count(*) from purchases)*100 as percentage
FROM purchases
GROUP BY date ( from_unixtime(submitted) );
您可以使用这样的两个嵌套查询(拆分成更多行并缩进以提高可读性)
SELECT
DAYOFWEEK(FROM_UNIXTIME(`submitted`)),
100 * COUNT(*) /
(SELECT COUNT(*) from `purchases`)
FROM
`purchases`
GROUP BY
DAYOFWEEK(FROM_UNIXTIME(`submitted`))
请注意,内部查询(SELECT COUNT(*) from purchases)
用于检索购买总数,因为您需要一个百分比数字。
mysql的DAYOFWEEK()
返回的不是当天的名称,而是一个从1到7的索引号。您可以在这里查找mysql日期和时间函数的参考:http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
如果你只需要一个查询,那么你就完成了。
如果您想使用该查询构建视图,则不能,因为mysql不允许在视图的定义中嵌套SELECT
。然后将作业拆分为两个视图,一个仅计算记录总数,另一个计算百分比。
关于性能的最后一点:
如果您有大量的记录要处理,那么执行速度不会很快。
如果您计划经常运行查询,这是不好的。
在这种情况下,由于submitted
很可能只写一次,而且从未更改过,因此您可以考虑在表中添加另一个名为submitted_dayofweek
的字段,键入int
。
当更新subitted
时,还要更新存储DAYOFWEEK(FROM_UNIXTIME(submitted))
的submitted_dayofweek
。
最后确保submitted_dayofweek
已编入索引。
查询变为
SELECT
`submitted_dayofweek`,
100 * COUNT(*) /
(SELECT COUNT(*) from `purchases`)
FROM
`purchases`
GROUP BY
`submitted_dayofweek`
并且将以使用更多存储空间为代价运行得非常快。