How to find the Sum of time difference by SQL

This article mainly introduces SQL how to find the sum of time difference, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The topics are as follows:

Ask for the number of promotion days for each brand

Table sale is a promotional marketing table, and there are duplicates in the data. For example, end_date with id 1 is 20180905 and start_date with ID 2 is 20180903, that is, duplicate sales dates with id 1 and id 2. Find out the promotion days of each brand (repetition is not counted).

The table results are as follows:

+-+ | id | brand | start_date | end_date | +-+ | 1 | nike | 2018-09-01 | 2018-09-05 | | 2 | nike | 2018-09-03 | | 2018-09-06 | | 3 | nike | 2018-09-09 | 2018-09-15 | | 4 | oppo | 2018-08-04 | 2018-08-05 | | 5 | oppo | 2018-08-04 | 2018-08-15 | 6 | vivo | 2018-08-15 | 2018-08-21 | 7 | vivo | 2018-09-02 | 2018-09-12 | +-+ |

The end result should be

Brandall_daysnike13oppo12vivo18

Construction table sentence

-Table structure for sale-- DROP TABLE IF EXISTS `sale`; CREATE TABLE `sale` (`id` int (11) DEFAULT NULL, `brand` varchar (255) DEFAULT NULL, `start_ date`date DEFAULT NULL, `end_ date`date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of sale-- INSERT INTO `sale`VALUES (1, 'nike',' 2018-09-01, '2018-09-05'); INSERT INTO `sale`VALUES (2,' nike', '2018-09-03,' 2018-09-06') INSERT INTO `sale` VALUES (3, 'nike',' 2018-09-09-09, '2018-09-15'); INSERT INTO `sale` VALUES (4, 'oppo',' 2018-08-04, '2018-08-05'); INSERT INTO `sale`VALUES (5, 'oppo',' 2018-08-04, '2018-08-15'); INSERT INTO `sale`VALUES (6, 'vivo',' 2018-08-15, '2018-08-21') INSERT INTO `sale` VALUES (7, 'vivo',' 2018-09-02, '2018-09-12')

Method 1:

Using the method of self-correlating the next record

Select brand,sum (end_date-befor_date+1) all_days from (select s.id, s.brand, s.start_date, s.end_date, if (s.start_date > = ifnull (t.enddatedtems.startbirthdate), s.startdispatdatedadd (t.enddatedateinterval 1 day) as befor_date from sale s left join (select id+1 as id, brand) End_date from sale) t on s.id = t.id and s.brand = t.brand order by s.id) tmp group by brand

Running result

+-+-+ | brand | all_day | +-+-+ | nike | 13 | oppo | 12 | vivo | 18 | +-+-+

This method is valid for the tables in this question, but it may not be suitable for records with id discontinuous brands.

Mode 2:

SELECT a.brand SUM (CASE WHEN a.start_date=b.start_date AND a.end_date=b.end_date AND NOT EXISTS (SELECT * FROM sale c LEFT JOIN sale d ON c.brand=d.brand WHERE d.brand=a.brand AND c.start_date=a.start_date AND c.idd.id AND (d.start_date BETWEEN c.start_date AND c.end_date AND d.end_date > c.end_date OR c.start_date BETWEEN d.start_date AND d.end_date) AND c.end_date > d.end_date) THEN (a.end_date-a.start_date+1) WHEN (a.idb.id AND b.start_date BETWEEN a.start_date AND a.end_date AND b.end_date > a.end_date) THEN (b.end_date-a.start_date+1) ELSE 0 END) AS all_days FROM sale a JOIN sale b ON a.brand=b.brand GROUP BY a.brand

Running result

+-+-+ | brand | all_days | +-+-+ | nike | 13 | oppo | 12 | vivo | 18 | +-+-+

Among them the condition

D.start_date BETWEEN c.start_date AND c.end_date AND d.end_date > c.end_date OR c.start_date BETWEEN d.start_date AND d.end_date AND c.end_date > d.end_date

It can be replaced with

C.start_date

< d.end_date AND (c.end_date >

D.start_date)

The result is equally correct.

It is also feasible to use the analysis function. I am not equipped with oracle on my computer for the time being. I wrote it with mysql.

Thank you for reading this article carefully. I hope the article "how to find the Sum of time difference in SQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!