CODEWITHSUNDEEP
sqlruby-on-railssqlitepostgresqlrails-activerecord
Don P
Don P

Reputation: 63718

Get a count of records created each week in SQL

I have a table Questions. How can I get a count of all questions asked in a week?

More generically, how can I bucket records by the week they were created in?

Questions
id     created_at            title            
----------------------------------------------------
1      2014-12-31 09:43:42   "Add things"
2      2013-11-23 02:98:55   "How do I ruby?"
3      2015-01-15 15:11:19   "How do I python?"
...

I'm using SQLLite, but PG answers are fine too.

Or if you have the answer using Rails ActiveRecord, that is amazing, but not required.

I've been trying to use DATEPART() but haven't come up with anything successful yet: http://msdn.microsoft.com/en-us/library/ms174420.aspx

Upvotes: 2

Views: 1237

Answers (4)

Mramaa
Mramaa

Reputation: 390

Get the week number using strfdate(%V) Store it in DB, and use it to identify in which week a question was asked

http://apidock.com/ruby/DateTime/strftime

SQL can do it too with the DATE_FORMAT(datetime,'%u')

So use:

SELECT DATE_FORMAT(column,'%u') FROM Table

Upvotes: 0

David Faber
David Faber

Reputation: 12485

In postgreSQL it's as easy as follows:

SELECT id, created_at, title, date_trunc('week', created_at) created_week
  FROM Questions

If you wanted to get the # of questions per week, simply do the following:

SELECT date_trunc('week', created_at) created_week, COUNT(*) weekly_cnt
  FROM Questions
 GROUP BY date_trunc('week', created_at)

Hope this helps. Note that date_trunc() will return a date and not a number (i.e., it won't return the ordinal number of the week in the year).

Update:

Also, if you wanted to accomplish both in a single query you could do so as follows:

SELECT id, created_at, title, date_trunc('week', created_at) created_week
     , COUNT(*) OVER ( PARTITION BY date_trunc('week', created_at) ) weekly_cnt
  FROM Questions

In the above query I'm using COUNT(*) as a window function and partitioning by the week in which the question was created.

Upvotes: 4

Brian DeMilia
Brian DeMilia

Reputation: 13248

If the created_at field is already indexed, I would simply look for all rows with a created_at value between X and Y. That way the index can be used.

For instance, to get rows with a created_at value in the 3rd week of 2015, you would run:

select *
  from questions
 where created_at between '2015-01-11' and '2015-01-17'

This would allow the index to be used.

If you want to be able to specify a week in the where clause, you could use the date_part or extract functions to add a column to this table storing the year and week #, and then index that column so that queries can take advantage of it.

If you don't want to add the column, you could of course use either function in the where clause and query against the table, but you won't be able to take advantage of any indexes.

Because you mentioned not wanting to add a column to the table, I would recommend adding a function based index.

For example, if your ddl were:

create table questions
(
  id int,
  created_at timestamp,
  title varchar(20)
);

insert into questions values
(1, '2014-12-31 09:43:42','"Add things"'),
(2, '2013-11-23 02:48:55','"How do I ruby?"'),
(3, '2015-01-15 15:11:19','"How do I python?"');


create or replace function to_week(ts timestamp)
   returns text
   as 'select concat(extract(year from ts),extract(week from ts))'
   language sql
   immutable
   returns null on null input;

create index week_idx on questions (to_week(created_at));

You could run:

select q.*, to_week(created_at) as week
  from questions q
 where to_week(created_at) = '20153';

And get:

| ID |                     CREATED_AT |              TITLE |  WEEK |
|----|--------------------------------|--------------------|-------|
|  3 | January, 15 2015 15:11:19+0000 | "How do I python?" | 20153 |

(reflecting the third week of 2015, ie. '20153')

Fiddle: http://sqlfiddle.com/#!15/c77cd/3/0

You could similarly run:

select q.*,
       concat(extract(year from created_at), extract(week from created_at)) as week
  from questions q
 where concat(extract(year from created_at), extract(week from created_at)) =
       '20153';

Fiddle: http://sqlfiddle.com/#!15/18c1e/3/0

But it would not take advantage of the function based index, because there is none. In addition, it would not use any index you might have on the created_at field because, while that field might be indexed, you really aren't searching on that field. You are searching on the result of a function applied against that field. So the index on the column itself cannot be used.

If the table is large you will either want a function based index or a column holding that week that is itself indexed.

Upvotes: 1

dSerk
dSerk

Reputation: 73

SQLite has no native datetime type like MS SQL Server does, so the answer may depend on how you are storing dates. Not all T-SQL will work in SQLite.

You can store datetime as an integer that counts seconds since 1/1/1970 12:00 AM. There are 604,800 seconds in a week. So you could query on an expression like

rawdatetime / 604800 -- iff rawdatetime is integer

More on handling datetimes in SQLite here: https://www.sqlite.org/datatype3.html

Upvotes: 0

Related Questions