When I was interviewing for a BI position at a certain company, there was a SQL question in the interview. It seemed very simple at first glance, but when I was writing it, I found that I lacked summary and could not write it out quickly. The topics are as follows: Find the number of promotion days for each brand Table sale is a promotional marketing table. There are repeated dates in the data. For example, the end_date of id 1 is 20180905, and the start_date of id 2 is 20180903. That is, id 1 and id 2 have repeated sales dates. Find the number of promotion days for each brand (duplicates are 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 final result should be
Create table statement -- ---------------------------- -- 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', '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-association to 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.end_date,s.start_date) ,s.start_date,DATE_ADD(t.end_date,interval 1 day) ) as before_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 Operation Results +-------+---------+ | brand | all_day | +-------+---------+ | nike | 13 | | oppo | 12 | | vivo | 18 | +-------+---------+ This method is valid for the table in this question, but may not be applicable to records of brands with discontinuous ids. Method 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.id<>d.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.id<>b.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 Operation Results +-------+----------+ | brand | all_days | +-------+----------+ | nike | 13 | | oppo | 12 | | vivo | 18 | +-------+----------+ Among the conditions 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 Can be replaced with c.start_date < d.end_date AND (c.end_date > d.start_date) The result is also correct It is also feasible to use analytical functions. I don’t have Oracle installed on my computer yet, so I wrote it in MySQL. The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM. You may also be interested in:
|
<<: Summary of common commands for Linux user and group management
>>: How to draw the timeline with vue+canvas
1. Introduction Whether the creation time of a fi...
1 / Copy the web project files directly to the we...
Table of contents 1. Master-slave replication Mas...
This time we will mainly learn about layout, whic...
Table of contents About G2 Chart use Complete cod...
In Linux, we usually use the mv command to rename...
Normally, you'll need to read everyone's s...
Table of contents Preface 1. Set the prototype on...
This article shares the specific code of fabricjs...
As one of the most popular front-end frameworks, ...
Table of contents 1. Swap partition SWAP 1.1 Crea...
The following is my judgment based on the data st...
MySQL 8 brings a brand new experience, such as su...
This article example shares the specific code of ...
Copy code The code is as follows: <div id=&quo...