過濾 created_at 併計算不同的分鐘數

我有這個查詢來從數據庫中獲取記錄：

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp 'yesterday'
AND    created_at <  timestamp 'today'
GROUP  BY camera_id;

我想添加一些過濾器created_at以count僅獲取匹配行。例如，使用者將提供camera_id, date, time, days, 例如：

From date: 2015/01/01 
to date: 2015/12/30
Schedule: Monday-Friday
Timings: 9 AM to 5 pm UTC

如何應用所有這些來created_at獲得有效的記錄？

更新：

我也想要每分鐘計數。如果 1 分鐘包含多於 1 個圖像，則仍應計為1 個。你可以說，只計算每分鐘的第一個快照。

很快，如果它是每 60 分鐘 1 次，那麼它將在上午 9 點到上午 10 點之間的 60 分鐘內計數 1 次，以此類推到下午 5 點。

由於缺乏資訊，假設created_at是 data type timestamp，保存 UTC 時間戳：

SELECT camera_id, count(*) AS snapshot_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'   -- From date: 2015/01/01 
AND    created_at <  timestamp '2015-12-31'   -- to date: 2015/12/30 
                                             -- chop off '2015-12-31'?
AND    created_at::time BETWEEN time '09:00' AND time '17:00'
                                             -- 9 AM to 5 AM UTC
                                             -- assuming you meant 5 PM
AND    EXTRACT('ISODOW' FROM created_at) < 6  -- Monday-Friday
GROUP  BY camera_id;

• 日期檢查似乎微不足道 - 但要小心在上下限中包含/排除的內容。
• 對於轉換為:的time組件。time``created_at::time
• 對於一周中的一天使用EXTRACT()該模式ISODOW（對於您的模式比 更簡單DOW）。

如果您不知道數據類型之間的區別timestamp以及設置和 DSTtimestamptz的作用timezone，現在是時候閱讀：

• 在 Rails 和 PostgreSQL 中完全忽略時區

將多行的分鐘數計為一

對於您添加的更新：

SELECT camera_id
    , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM ...

實際上，計算至少拍攝一張（過濾的）快照的分鐘數。如果在同一分鐘內拍攝了 7 個快照，它們仍然算作1 個。

表現

時間和星期幾的過濾器是不可搜尋的。如果您的表很大並且性能很重要，請創建功能索引來改變它 - 使用IMMUTABLE表達式。根據您的實際數據類型，您需要創建一個IMMUTABLE獨立於時區設置工作的包裝函式，因為由於各種原因，既沒有定義時間轉換，也沒有定義EXTRACT()（內部函式date_part()）IMMUTABLE。

CREATE FUNCTION f_to_time(timestamp)
 RETURNS time LANGUAGE sql IMMUTABLE COST 5 AS
'SELECT $1::time';

CREATE OR REPLACE FUNCTION f_to_isodow(timestamp)
 RETURNS int LANGUAGE sql IMMUTABLE COST 20 AS
$$SELECT EXTRACT('ISODOW' FROM $1 AT TIME ZONE 'UTC')::int$$;

然後：

CREATE INDEX snapshots_created_at_time_idx ON snapshots (f_time(created_at));
CREATE INDEX snapshots_created_at_isodow_idx ON snapshots (f_to_isodow(created_at));

要利用這些索引，查詢必須使用相同的表達式：

SELECT camera_id
    , count(*) AS snapshot_count
    , count(DISTINCT date_trunc('minute', created_at) AS snapshot_minute_count
FROM   snapshots
WHERE  created_at >= timestamp '2015-01-01'
AND    created_at <  timestamp '2015-12-31'
AND    f_time(created_at) BETWEEN time '09:00' AND time '17:00'
AND    f_to_isodow(created_at) < 6
GROUP  BY camera_id;

引用自：https://dba.stackexchange.com/questions/129052