Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to easily support time frame grouping in queries?
I had a curiosity about how much the experienced users wait for their questions to be answered on Stack Overflow and had written a query for it:
SELECT YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM, COUNT(1) Cnt
INTO #a_cte
FROM Posts AS q
INNER JOIN Users qu ON qu.Id = q.OwnerUserId and qu.Reputation >= 1000
WHERE q.CreationDate BETWEEN '20190101' AND '20210301'
AND EXISTS (
SELECT 1
FROM Posts a
where a.PostTypeId = 2 and a.ParentId = q.Id and a.Score > 1
and datediff(day, q.CreationDate, a.CreationDate) <= 7
)
AND q.PostTypeId = 1
GROUP BY YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate);
SELECT YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM, COUNT(1) Cnt
INTO #q_cte
FROM Posts AS q
INNER JOIN Users qu ON qu.Id = q.OwnerUserId and qu.Reputation >= 1000
WHERE q.CreationDate BETWEEN '20190101' AND '20210301'
AND q.PostTypeId = 1
GROUP BY YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate);
SELECT CAST(a.YM / 100 AS VARCHAR) + '-' + CAST(a.YM % 100 AS VARCHAR) + '-01', a.Cnt * 100.0 / q.Cnt AS AnsweredRatio
FROM #a_cte a
JOIN #q_cte q ON q.YM = a.YM
ORDER BY a.YM
The performance is rather poor and I think it is also related to the fact that I have to compute the year-month and also group by it. This should have been much easier if the data model supplied some "dimension" columns such as year, month, year-month.
I know that enterprise solutions involve reporting databases and cubes, but I am wondering about simpler solutions (less maintenance for medium-sized data volumes). Is adding of computed columns a good way to support the queries I want. Examples:
CreationDateYear = YEAR(CreationDate)YM = YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM
Or maybe a better approach is to have a job that computes similar columns in separate tables (as opposed to the real-time nature of the computed columns).
1 answer
In SQL Server 2022+, there's a dedicated binning function called DATE_BUCKET.
Each of these statements increments
DATE_BUCKETwith a bucket width of1from the origin time:DECLARE @date DATETIME2 = '2020-04-30 21:21:21'; SELECT 'Week', DATE_BUCKET(WEEK, 1, @date) UNION ALL SELECT 'Day', DATE_BUCKET(DAY, 1, @date) UNION ALL SELECT 'Hour', DATE_BUCKET(HOUR, 1, @date) UNION ALL SELECT 'Minutes', DATE_BUCKET(MINUTE, 1, @date) UNION ALL SELECT 'Seconds', DATE_BUCKET(SECOND, 1, @date);Here's the result set.
Week 2020-04-27 00:00:00.0000000 Day 2020-04-30 00:00:00.0000000 Hour 2020-04-30 21:00:00.0000000 Minutes 2020-04-30 21:21:00.0000000 Seconds 2020-04-30 21:21:21.0000000
In older versions of SQL Server, I did date binning with DATEADD and DATEDIFF, rather than offsetting integers or casting to a string.
Taking your second faux-CTE temporary table, it would look like this:
SELECT DATEADD(MONTH, FLOOR(DATEDIFF(MONTH, '2000', q.CreationDate)), '2000') AS bin,
COUNT(1) Cnt
INTO #q_cte
FROM Posts AS q
INNER JOIN Users AS qu
ON qu.Id = q.OwnerUserId
AND qu.Reputation >= 1000
WHERE q.CreationDate BETWEEN '20190101' AND '20210301'
AND q.PostTypeId = 1
GROUP BY DATEDIFF(MONTH, '2000', q.CreationDate)
I'm not sure if it's faster, but I think you could write your query with a single pass through the Question posts. Record whether each one is answered or not, and group those.
SELECT q.ID, -- Not actually necessary. Just a helpful check.
DATEADD(MONTH, ...) AS bin,
COALESCE((SELECT TOP(1) 1 FROM ...), 0) AS is_answered
INTO #q_cte
FROM Posts AS q
WHERE ...;
SELECT bin, 100. * SUM(is_answered) / COUNT(*) AS ratio
FROM #q_cte
GROUP BY bin
0 comment threads
1 comment thread