How do I GROUP BY at each given increment of a field value?
I have a Python application. It has a SQLite database full of data about things that are happening, retrieved by a web scraper from the Internet. This data includes time-time groups such as Unix timestamps in a column reserved for them. I want to get the names of the organizations that did things and count how often they did them, but for that for each week (i.e. 604,800 seconds) I have data for.
pseudocode:
for each 604800-second increment in time:
select count(time), org from table group by org
Basically, I'm trying to iterate through a database, such as a list sorted by a column of time, in increments of 604800. The goal is to analyze how the distribution of different organizations as a whole has changed over time.
If at all possible, I would like to not pull all the rows from the db and process them in Python, as that seems a) inefficient and b) probably pointless given that the data is in the database.
a source to share
Not familiar with SQLite. I think this approach should work for most databases as it finds the week number and subtracts the offset
SELECT org, ROUND(time/604800) - week_offset, COUNT(*)
FROM table
GROUP BY org, ROUND(time/604800) - week_offset
In Oracle, I would use the following if the time was a date column:
SELECT org, TO_CHAR(time, 'YYYY-IW'), COUNT(*)
FROM table
GROUP BY org, TO_CHAR(time, 'YYYY-IW')
SQLite probably has similar functionality which allows for this kind of SELECT, which is easier on the eye.
a source to share
Create a table listing all weeks from the epoch and JOIN
an event table.
CREATE TABLE Weeks (
week INTEGER PRIMARY KEY
);
INSERT INTO Weeks (week) VALUES (200919); -- e.g. this week
SELECT w.week, e.org, COUNT(*)
FROM Events e JOIN Weeks w ON (w.week = strftime('%Y%W', e.time))
GROUP BY w.week, e.org;
There are only 52-53 weeks a year. Even if you've been filling the Weeks table for 100 years, it's still a small table.
a source to share
To do this with a set (which SQL works well for), you will need a set-based representation of your time increments. It can be a temporary table, a permanent table, or a view (i.e. a subquery). I am not very good at SQLite and have worked with UNIX since then. Are timestamps in UNIX just # seconds from some set date / time? Using a standard calendar table (which is useful to have in the database) ...
SELECT
C1.start_time,
C2.end_time,
T.org,
COUNT(time)
FROM
Calendar C1
INNER JOIN Calendar C2 ON
C2.start_time = DATEADD(dy, 6, C1.start_time)
INNER JOIN My_Table T ON
T.time BETWEEN C1.start_time AND C2.end_time -- You'll need to convert to timestamp here
WHERE
DATEPART(dw, C1.start_time) = 1 AND -- Basically, only get dates that are a Sunday or whatever other day starts your intervals
C1.start_time BETWEEN @start_range_date AND @end_range_date -- Period for which you're running the report
GROUP BY
C1.start_time,
C2.end_time,
T.org
The calendar table can take any shape you want, so you can use UNIX timestamps in it for start_time and end_time. You just pre-fill it with all the dates in whatever possible range you might want to use. Even going from 1900-01-01 to 9999-12-31 would not be an awfully large table. This can be useful for a large number of queries such as reports.
Finally, this code is T-SQL, so you probably need to convert DATEPART and DATEADD to any SQLite equivalent.
a source to share