sql - 创建自定义事件时间表。我应该使用 "LIKE"吗？

Question

我正在创建一个事件事件调度器，它允许诸如“每周一”、“5 月 6 日至 10 日”、“除周日外的每一天”等频率。

我提出了一个我认为可以正常工作的解决方案(尚未实现)，但是，它在查询中使用了“LIKE”，我一直不太喜欢。如果其他人有可以使用更清洁的方法达到相同结果的建议，请提出建议!

+----------------------+
| Campaign Table       |
+----------------------+
| id:int               |
| event_id:foreign_key |
| start_at:datetime    |
| end_at:datetime      |
+----------------------+

+-----------------------------+
| Event Table                 |
+-----------------------------+
| id:int                      |
| valid_days_of_week:string   |  < * = ALL. 345 = Tue, Wed, Thur. etc.
| valid_weeks_of_month:string |  < * = ALL. 25 = 2nd and 5th weeks of a month.
| valid_day_numbers:string    |  < * = ALL. L = last. 2,7,17,29 = 2nd day, 7th, 17th, 29th,. etc.
+-----------------------------+

事件日程示例如下所示:

valid_days_of_week = '1357' (Sun, Tue, Thu, Sat)
valid_weeks_of_month = '*' (All weeks)
valid_day_numbers = ',1,2,5,6,8,9,25,30,'

以今天(6/25/15)为例，我们有以下信息可以查询:

Day of week: 5 (Thursday)
Week of month: 4 (4th week in June)
Day number: 25

因此，要获取今天的所有事件，查询将如下所示:

SELECT c.*
  FROM campaigns AS c,
  LEFT JOIN events AS e
    ON c.event_id = e.id
WHERE
  ( e.valid_days_of_week = '*' OR e.valid_days_of_week LIKE '%5%' )
    AND ( e.valid_weeks_of_month = '*' OR e.valid_weeks_of_month LIKE '%4%' )
    AND ( e.valid_day_numbers = '*' OR e.valid_day_numbers LIKE '%,25,%' )

该(未经测试的)查询理想情况下会返回上面的示例事件。 “LIKE”查询让我担心。我希望这些查询更快。

顺便说一下，我正在使用 PostgreSQL

期待优秀的回复!

Answer 1

使用数组:

CREATE TABLE events (id INT NOT NULL, dow INT[], wom INT[], dn INT[]);

CREATE INDEX ix_events_dow ON events USING GIST(dow);
CREATE INDEX ix_events_wom ON events USING GIST(wom);
CREATE INDEX ix_events_dn ON events USING GIST(dn);

INSERT
INTO    events
VALUES  (1, '{1,3,5,7}', '{0}', '{1,2,5,6,8,9,25,30}'); -- 0 means any

，然后查询:

SELECT  *
FROM    events
WHERE   dow && '{0, 5}'::INT[]
        AND wom && '{0, 4}'::INT[]
        AND dn && '{0, 26}'::INT[]

这将允许使用索引来过滤数据。