Mysql View (最近六个月的数据)

Question

是否有可能创建过去六个月或任何其他持续时间的 View 。

因为我想避免每次查询10亿条记录

SELECT c.CONTACT_ID,c.CONTACT_EMAIL,l.MAPPING_TIME FROM ContactInformation as c INNER JOIN ListToContactMapping as l ON c.CONTACT_ID=l.CONTACT_ID LEFT JOIN ContactScoreByModule as cs ON c.CONTACT_ID=cs.CONTACT_ID WHERE  ((((((l.MAPPING_TIME >= 1519822800000) AND (l.MAPPING_TIME <= 1522504799000)) AND ((c.CONTACT_STATUS =  BINARY 'active') OR (c.CONTACT_STATUS =  BINARY 'softbounce'))) AND (l.LIST_ID = 5000000004001)) AND (l.STATUS =  BINARY 'subscribed')))  GROUP BY c.CONTACT_ID LIMIT 0,21;

目前我们仅获得静态查询

日期列以毫秒为单位。

+---------------+---------------------------+---------------+
| CONTACT_ID    | CONTACT_EMAIL             | MAPPING_TIME  |
+---------------+---------------------------+---------------+
| 5000000004052 | arunkumar.muthu+9@abc.com | 1520819541000 |
| 5000000004053 | arunkumar.muthu+8@abc.com | 1520819541000 |
| 5000000004054 | arunkumar.muthu+7@abc.com | 1520819541000 |
| 5000000004055 | arunkumar.muthu+6@abc.com | 1520819541000 |
| 5000000004056 | arunkumar.muthu+5@abc.com | 1520819541000 |
| 5000000004057 | arunkumar.muthu+4@abc.com | 1520819541000 |
| 5000000004058 | arunkumar.muthu+3@abc.com | 1520620200000 |
| 5000000004059 | arunkumar.muthu+2@abc.com | 1520620200000 |
+---------------+---------------------------+---------------+

Answer 1

使用 DATE_SUB() 获取最近 6 个月的数据

 CREATE VIEW View_Name AS SELECT col1,col2,.... FROM Table_Name
 WHERE date_column > DATE_SUB(now(), INTERVAL 6 MONTH)

以毫秒为单位

SELECT CONTACT_ID,CONTACT_EMAIL,
FROM_UNIXTIME(LEFT( MAPPING_TIME,length(MAPPING_TIME)-3)) AS Date
FROM Table1
WHERE FROM_UNIXTIME(LEFT( MAPPING_TIME,length(MAPPING_TIME)-3)) > 
DATE_SUB( FROM_UNIXTIME(LEFT( MAPPING_TIME,length(MAPPING_TIME)-3)), INTERVAL 6 MONTH)

检查链接

http://sqlfiddle.com/#!9/65f20d/12