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.
statis_mall_hourly_detail,CREATETABLE`statis_mall_hourly_detail`(`mall_id`int(11)NOTNULLDEFAULT'0'COMMENT'mall id',`floor_id`int(11)NOTNULLDEFAULT'0'COMMENT'floor id，-1 for all floors',`user_type`char(1)NOTNULLDEFAULT''COMMENT'user type: a for all, n for new, r for regular',`dt`datetimeNOTNULLDEFAULT'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`datetimeDEFAULTNULLONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`mall_id`,`floor_id`,`user_type`,`dt`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='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.
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.