The data I got is

id date cnt
1 '2021-11-26' 1
1 '2021-11-25' 1
1 '2021-11-24' 1
1 '2021-11-23' 1

The result I want is

id from_date to_date cnt
1 '2021-11-23' '2021-11-24' 2
1 '2021-11-23' '2021-11-25' 3
1 '2021-11-23' '2021-11-26' 4
1 '2021-11-24' '2021-11-25' 2
1 '2021-11-24' '2021-11-26' 3
1 '2021-11-25' '2021-11-26' 2

Is there a way to generate those dates (from_date, to_date) from the date column in the data?

Do I need to use loop from the iteration of the date?

What else I can think of to get close to the result?

Thanks in advance


Is there a way to generate those dates (from_date, to_date)

A "triangular join":

SELECT a.date AS from_date,
       z.date AS to_date
    FROM t AS a
    WHERE a.date < z.date

That can then be joined again to t to deal with the sums.

  • That's more of a triangular join. You may as well use proper join syntax with an ON clause Nov 28 '21 at 10:38
  • Hmmm... I learned something new. MySQL never mentions "triangular joins".
    – Rick James
    Nov 28 '21 at 17:18
  • No it wouldn't there is no special syntax, just JOIN t AS z ON a.date < z.date Nov 28 '21 at 17:40
  • You saved my days! Thanks a lot!
    – Eric Lee
    Nov 29 '21 at 3:31

Assuming a simple table structure like this:

    `id`            int(11)        UNSIGNED   NOT NULL    AUTO_INCREMENT,
    `date`          timestamp                 NOT NULL    ,
    `cnt`           int(11)        UNSIGNED   NOT NULL    DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

With data like this:

INSERT INTO `Sample` (`date`)
VALUES ('2021-11-26'),

You can do this:

SELECT tmp.`id`,
       DATE_FORMAT(tmp.`from_dts`, '%Y-%m-%d') as `from_date`,
       DATE_FORMAT(tmp.`to_dts`, '%Y-%m-%d') as `to_date`,
       SUM(sm.`cnt`) as `cnt`
  FROM `Sample` sm INNER JOIN (SELECT a.`id`, DATE_FORMAT(a.`date`, '%Y-%m-%d 00:00:00') as `from_dts`, DATE_FORMAT(z.`date`, '%Y-%m-%d 23:59:59') as `to_dts`
                                 FROM `Sample` a INNER JOIN `Sample` z ON a.`date` < z.`date`) tmp ON sm.`date` BETWEEN tmp.`from_dts` AND tmp.`to_dts`
 GROUP BY tmp.`id`, `from_date`, `to_date`
 ORDER BY `from_date`, `to_date`;

There are certainly other ways to accomplish this same goal but, this will do in a pinch. If you are going to be running this sort of query against millions of records, though, you will want to approach the problem very differently.

  • Somehow the summed cnt shows more than I expected. but the range of the date were generated correctly.
    – Eric Lee
    Nov 29 '21 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.