Gangmax Blog

A Typical Scenario to Use Union Select SQL

A table in MySQL database which saves the “user_count/shop_user_count” for specific place(“mall_id/floor_id”) and time(“dt”). The table structure is listed below.

1
2
3
4
5
6
7
8
9
10
statis_mall_hourly_detail, CREATE TABLE `statis_mall_hourly_detail` (
  `mall_id` int(11) NOT NULL DEFAULT '0' COMMENT 'mall id',
  `floor_id` int(11) NOT NULL DEFAULT '0' COMMENT 'floor id，-1 for all floors',
  `user_type` char(1) NOT NULL DEFAULT '' COMMENT 'user type: a for all, n for new, r for regular',
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'date time in format \"yyyy-MM-dd HH:mm\"',
  `user_count` int(11) DEFAULT '0' COMMENT 'passengers count of the mall',
  `shop_user_count` int(11) DEFAULT '0' COMMENT 'passengers count who enters any shop of the mall',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`mall_id`,`floor_id`,`user_type`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='passengers statistics data for each hour of mall'

The original requirment is to query records for a specific time range(a day, a week and so on). It can be done by one “select” SQL easily.

1
2
3
select DATE_FORMAT(dt,'%Y-%m-%d') as date_part, DATE_FORMAT(dt,'%H:%i') as hour_part, user_count,
 shop_user_count from statis_mall_hourly_detail where mall_id =1 and user_type = 'a'
 and floor_id = -1 and dt >= '2017-09-09 00:00' and dt < '2017-09-10 00:00' order by date_part, hour_part

And then the new requirement comes: query records for some time ranges, typically for some given days. For an example, I want the records of last Monday, Wednesday and Friday. How to make the query in SQL?

First, it’s not a good idea to use a “dt in (…)” WHERE condition since it’s too long and too verbose. And yes, it can be done by use multiple select SQL statements listed above, but it’s performance is poor. And then I realize it’s a typical scenario to use “union select” SQL statement.

1
2
3
4
5
6
7
8
9
10
11
(select DATE_FORMAT(dt,'%Y-%m-%d') as date_part, DATE_FORMAT(dt,'%H:%i') as hour_part, user_count,
  shop_user_count from statis_mall_hourly_detail where mall_id =1 and user_type = 'a'
  and floor_id = -1 and dt >= '2017-09-09 00:00' and dt < '2017-09-10 00:00' order by date_part, hour_part)
union distinct
(select DATE_FORMAT(dt,'%Y-%m-%d') as date_part, DATE_FORMAT(dt,'%H:%i') as hour_part, user_count,
  shop_user_count from statis_mall_hourly_detail where mall_id =1 and user_type = 'a'
  and floor_id = -1 and dt >= '2017-09-10 00:00' and dt < '2017-09-11 00:00' order by date_part, hour_part)
union distinct
(select DATE_FORMAT(dt,'%Y-%m-%d') as date_part, DATE_FORMAT(dt,'%H:%i') as hour_part, user_count,
  shop_user_count from statis_mall_hourly_detail where mall_id =1 and user_type = 'a'
  and floor_id = -1 and dt >= '2017-09-20 00:00' and dt < '2017-09-21 00:00' order by date_part, hour_part)

Such SQL statement can be generated by program with the given arguments(“mall_id, user_type, floor_id and date ranges”).

Refer here

Comments