samedi 30 avril 2016

Add Columns to a SQL Row from Count of Unique Values in One Column

I have two columns I am interested in:

bought_time           |    product
1990-10-26T09:00:00Z  | bicycle
1990-10-26T09:00:01Z  | car
1990-10-26T09:13:22Z  | bicycle
1990-10-26T09:26:11Z  | cookies
1990-10-26T09:26:13Z  | milk
1990-10-26T09:27:16Z  | milk

I want to create a SQLite query in Ruby on Rails that will return this after being converted by as_json:

[{date by minute: 1990-10-26T09:00:00Z, bicycle: 1, car: 1, cookies:0, milk:0}, {date by minute: 1990-10-26T09:13:00Z, bicycle:1, car:0, cookies:0, milk:0}, {date by minute: 1990-10-26T09:26:00Z, bicycle:0, car:0, cookies:1, milk:1}, {date by minute: 1990-10-26T09:26:00Z, bicycle:0, car:0, cookies:0, milk:1} ]

So far I have something like:"datetime((strftime('%s', date)/60)*60, 'unixepoch') interval, count(type)").where("date(date) >= date(:from) AND date(date) <= date(:to)", {from: event_params["from"], to: event_params["to"]}).order("interval").group("type").group("interval")

Which in SQL is

SELECT datetime((strftime('%s', date)/60)*60, 'unixepoch') interval, count(type), type FROM "events" WHERE (date(date) >= date('1985-10-26T09:00:00Z') AND date(date) <= date('1985-10-27T09:00:00Z')) GROUP BY interval, "events"."type"  ORDER BY interval

but I am at a loss at how to proceed from here.

Aucun commentaire:

Enregistrer un commentaire