RubyonRails implements the process of aggregating (by time) articles.
Currently, I would like to run SQL such as the following, save the aggregated results to DB, and display them on the web screen.
SQL
SELECT date_format(created_at, '%Y-%m-%d-%H') as hour, count(id)
FROM articles WHERE(created_at>="2016-05-29"AND created_at<"2016-05-30")
GROUP BY hour;
tabulation table
Report
id(integer)
hour(datetipe)
count(integer)
I'm still thinking about saving to DB after running SQL, so I haven't been able to implement it yet. Currently, the problem is the hour(datetipe) part.
This is because when aggregating, date_format(created_at, '%Y-%m-%d-%H') is used. You will not be able to save it to DB.
I will explain the reason why it was shaped like %Y-%m-%d-%H later, but I wanted to include characters recognizing the break in order to make it look like Wednesday, May 29, 2016.
There are two ways I thought about that.
1) change the hour to the string 2) Do not use date_format(created_at, '%Y-%m-%d-%H') when executing SQL, but successfully aggregate by time
1) When saving in the string type and displaying the screen, use the controller
I think it would be good to add the process of converting from string to datetime type.
(The reason for the conversion is to get the day of the week.)
Also, on the screen side,
Wednesday, May 29, 2016
I would like to make it look like this.
1) I think I can do it if I try my best, but I feel uncomfortable saving the date with the string type.
However, I am having trouble with 2) because I do not know how to do it.
Based on the above, I would appreciate your advice.
Thank you for your cooperation.
The subject matter was resolved by metropolis's response.
In order to aggregate by time, we have a way to convert the aggregation key from DATE_FORMAT to %Y-%m-%d%H:00:00 and cast it into a DATETIME type.
However, in the future, if you want to count by month, the tally key will look like (2016-04).
In this case, I thought it would be impossible to cast DATETIME or DATE.
In this case, is there no choice but to save it as a String type?
ruby-on-rails ruby mysql sql
For PostgreSQL, date_trunc
https://www.postgresql.jp/document/9.5/html/functions-datetime.html#functions-datetime-trunc
I think you can get the desired value by aggregating it in the column you ran
I will also leave a SQL sample.
http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9925
WITH ARTICLES AS(
SELECT
id, created_at
FROM(
VALUES
(1, TIMESTAMP'2016-07-1500:00:00'),
(2, TIMESTAMP'2016-07-1500:01:00'),
(3, TIMESTAMP'2016-07-1501:00:00'),
(4, TIMESTAMP'2016-07-1501:01:00'),
(5, TIMESTAMP'2016-07-1501:02:00'),
(6, TIMESTAMP'2016-07-1502:00:00')
AST(id, created_at)
)
SELECT
hour_trunc,
count(*)AS hour_count
FROM(
SELECT
id,
date_trunc('hour', created_at)AS hour_trunc,
count(created_at)over(partition by date_trunc('hour', created_at))
from
articles
)T
GROUP BY
hour_trunc
ORDER BY
hour_trunc
;
If there are things that don't work well related to aggregation, I think using window function can solve many problems.
Try it if you have a chance
(But I don't think they will generate SQLbuilders for Rails...)
Mysql seems to have no choice but to do TIME_FORMAT or take the remainder of TO_SECONDS...
http://sqlfiddle.com/#!9/9eecb7d/69182
SELECT
sec,
FROM_UNIXTIME(sec*3600-TO_SECONDS('1970-01-0100:00:00') as my_date_time,
count(sec)AS count
FROM(
SELECT
id,
updated_at,
CAST(TO_SECONDS(updated_at)/3600AS SIGNED INTEGER) ASsec,
TIME_FORMAT(updated_at, '%Y%m%d%h')ASymdh
FROM(
SELECT1 AS id, TIMESTAMP'2016-07-1500:00:00'AS updated_at FROM DUAL
UNION ALL SELECT 2, TIMESTAMP'2016-07-1500:01:00' FROM DUAL
UNION ALL SELECT 3, TIMESTAMP'2016-07-1501:00' FROM DUAL
UNION ALL SELECT 4, TIMESTAMP'2016-07-1501:01:00' FROM DUAL
UNION ALL SELECT 5, TIMESTAMP'2016-07-1501:02:00' FROM DUAL
UNION ALL SELECT 6, TIMESTAMP'2016-07-1502:00' FROM DUAL
AST
AST
GROUP BY
sec
ORDER BY
sec
;
© 2024 OneMinuteCode. All rights reserved.