CODEWITHSUNDEEP
mysqlsqldatabase
Dmitry
Dmitry

Reputation: 191

Is it possible to group by a few different date periods in mysql?

There is a table likes:

like_user_id | like_post_id | like_date
----------------------------------------
1            | 2            | 1399274149
5            | 2            | 1399271149
....
1            | 3            | 1399270129

I need to make one SELECT query and count records for specific like_post_id by grouping according periods for 1 day, 7 days, 1 month, 1 year.

The result must be like:

period  | total
---------------
1_day   | 2
7_days  | 31
1_month | 87
1 year  | 141

Is it possible?

Thank you.

Upvotes: 0

Views: 439

Answers (4)

Joseph B
Joseph B

Reputation: 5669

Here is an alternative using CROSS JOIN. First, the time difference is calculated using the TIMESTAMPDIFF function and the appropriate parameter (DAY/WEEK/MONTH/YEAR). Then, if the counts are equal to 1, then the value is added up. Finally, the CROSS JOIN is made with an inline view containing the names of the periods.

SELECT
  periods.period,
  CASE periods.period
    WHEN '1_day' THEN totals.1_day
    WHEN '7_days' THEN totals.7_days
    WHEN '1_month' THEN totals.1_month
    WHEN '1_year' THEN totals.1_year
  END total
FROM
(
SELECT
  SUM(CASE days WHEN 2 THEN 1 ELSE 0 END) 1_day,
  SUM(CASE weeks WHEN 1 THEN 1 ELSE 0 END) 7_days,
  SUM(CASE months WHEN 1 THEN 1 ELSE 0 END) 1_month,
  SUM(CASE years WHEN 1 THEN 1 ELSE 0 END) 1_year
FROM
(
SELECT
      TIMESTAMPDIFF(YEAR, FROM_UNIXTIME(like_date), NOW()) years,
      TIMESTAMPDIFF(MONTH, FROM_UNIXTIME(like_date), NOW()) months,
      TIMESTAMPDIFF(WEEK, FROM_UNIXTIME(like_date), NOW()) weeks,
      TIMESTAMPDIFF(DAY, FROM_UNIXTIME(like_date), NOW()) days
FROM likes
) counts
) totals
CROSS JOIN
(
  SELECT 
  '1_day' period
  UNION ALL
  SELECT 
  '7_days'
  UNION ALL
  SELECT 
  '1_month'
  UNION ALL
  SELECT 
  '1_year'
) periods

Upvotes: 0

flaschenpost
flaschenpost

Reputation: 2235

To be flexible, simply make a table time_intervals which holds from_length and to_length in seconds:

CREATE TABLE time_intervals 
(   id int(11) not null auto_increment primary key,
    name varchar(255),
    from_seconds int,
    to_seconds int
);

The select is then quite straight:

select like_post_id, ti.name as interval, count(*) as cnt_likes
from time_intervals ti 
left /* or inner */ join likes on likes.like_post_id = 175 
     and likes.like_date between unix_timestamp(now()) -  ti.to_seconds and unix_timestamp(now()) + ti.from_seconds 
group by ti.id

With left join you get always all intervals (even when holes exist), with inner join only the intervals which exist.

So you change only table time_intervals and can get what you want. The "175" stands for the post you want, and of course you can change to where ... in () if you want.

Upvotes: 1

Steph Locke
Steph Locke

Reputation: 6146

This code shows how to build a cross-tab style query that you will likely need. This aggregates by like_post_id and you may want to put restrictions on it. Further, in terms of last month I don't know whether you mean month to date, last 30 days or last calendar month so I've left that to you.

SELECT
like_post_id,
-- cross-tab example, rinse and repeat as required
-- aside of date logic, the SUM(CASE logic is designed to be ANSI compliant but you could use IF instead of CASE
SUM(CASE WHEN FROM_UNIXTIME(like_date)>=DATE_SUB(CURRENT_DATE(), interval 1 day) THEN 1 ELSE 0 END) as 1_day,
...
FROM likes
-- to restrict the number of rows considered
WHERE FROM_UNIXTIME(like_date)>=DATE_SUB(CURRENT_DATE(), interval 1 year) 
GROUP BY like_post_id

Upvotes: 1

Vikas Hardia
Vikas Hardia

Reputation: 2695

I have a created a query for Oracle syntax please change it according to your db

select '1_Day' as period , count(*) as Total
from likes
where like_date>(sysdate-1)
union
select '7_days'  , count(*)
from likes
where like_date>(sysdate-7)
union
select '1_month'  , count(*)
from likes
where like_date>(sysdate-30)
union
select '1 year'  , count(*)
from likes
where like_date>(sysdate-365)

here idea is to get single sub query for single period and apply the filter in where to match the filter.

Upvotes: 1

Related Questions