我们有一个统计数据库,我们希望对一些结果进行分组。每个条目都有时间戳“tstarted”。
我们想按一天的每一个季度分组。对于每个季度,我们想知道哪一天有超过0的结果(对于那个季度)。
我们可以通过使用子查询来解决这个问题:
select quarter, sum(q), count(quarter), sum(q) / count(quarter) as average
from (
select SEC_TO_TIME((TIME_TO_SEC(tstarted) DIV 900) * 900) as quarter, sum(qdelivered) as q
from statistics
where stat_field = 1
group by SEC_TO_TIME((TIME_TO_SEC(tstarted) DIV 900) * 900), date(tstarted)
order by SEC_TO_TIME((TIME_TO_SEC(tstarted) DIV 900) * 900) asc
) as sub
group by quarter我的问题是:是否有更有效的方法来检索这个结果(例如联接或其他方式)?
发布于 2014-06-30 16:27:53
我很想在一天内摆上一张25美分的桌子。使用此表并左转加入您的统计表它。
CREATE TABLE quarters
(
id INT,
start_qtr INT,
end_qtr INT
);
INSERT INTO quarters (id, start_qtr, end_qtr) VALUES
(1,0,899),
(2,900,1799),
(3,1800,2699),
(4,2700,3599),
(5,3600,4499),
(6,4500,5399),
(7,5400,6299),
(8,6300,7199),
etc;您的查询可以是:-
SELECT SEC_TO_TIME(quarters.start_qtr) AS quarter,
sum(statistics.qdelivered),
count(statistics.qdelivered),
sum(statistics.qdelivered) / count(statistics.qdelivered) as average
FROM quarters
LEFT OUTER JOIN statistics
ON TIME_TO_SEC(statistics.tstarted) BETWEEN quarters.start_qtr AND quarters.end_qtr
AND statistics.stat_field = 1
AND DATE(statistics.tstarted) = '2014-06-30'
GROUP BY quarter
ORDER BY quarter;这样做的好处是,对于没有统计数据的季度,它将给出计数为0(平均为NULL)的条目,并保存一些计算。
您可以通过在季度表中添加时间列来节省更多的计算:
CREATE TABLE quarters
(
id INT,
start_qtr INT,
end_qtr INT
start_qtr_time TIME,
end_qtr_time TIME,
);
INSERT INTO quarters (id, start_qtr, end_qtr, start_qtr_time, end_qtr_time) VALUES
(1,0,899, '00:00:00', '00:14:59'),
(2,900,1799, '00:15:00', '00:29:59'),
(3,1800,2699, '00:30:00', '00:44:59'),
(4,2700,3599, '00:45:00', '00:59:59'),
(5,3600,4499, '01:00:00', '01:14:59'),
(6,4500,5399, '01:15:00', '01:29:59'),
(7,5400,6299, '01:30:00', '01:44:59'),
(8,6300,7199, '01:45:00', '01:59:59'),
etc这样就节省了联接上函数的使用:-
SELECT start_qtr_time AS quarter,
sum(statistics.qdelivered),
count(statistics.qdelivered),
sum(statistics.qdelivered) / count(statistics.qdelivered) as average
FROM quarters
LEFT OUTER JOIN statistics
ON TIME(statistics.tstarted) BETWEEN quarters.start_qtr_time AND quarters.end_qtr_time
AND statistics.stat_field = 1
AND DATE(statistics.tstarted) = '2014-06-30'
GROUP BY quarter
ORDER BY quarter;这两种假设都假设你对某一天感兴趣。
https://stackoverflow.com/questions/24493768
复制相似问题