如何对字段值的每个增量进行分组？

1 投票

3 回答

1029 浏览

提问于 2025-04-15 11:34

我有一个用Python写的应用程序，它使用SQLite数据库，里面存储着通过网络爬虫从网上抓取的数据。这些数据包括时间和日期，以Unix时间戳的形式存储在专门的列里。我想提取出做事情的组织名称，并统计他们做事情的次数，但我想按每周来统计（也就是604,800秒），我有这些数据。

伪代码：

for each 604800-second increment in time:
 select count(time), org from table group by org

简单来说，我想像处理一个按时间列排序的列表一样，逐行遍历数据库，每次跳过604800秒。我的目的是分析不同组织在总数据中随时间变化的分布情况。

如果可能的话，我希望避免从数据库中提取所有行，然后在Python中处理，因为这样做一方面效率低下，另一方面考虑到数据已经在数据库中，这样做似乎没有必要。

数据库 sqlite 数据分析网络爬虫时间序列数据分组 unix时间戳组织统计

3 个回答

要用一种基于集合的方式来处理这个问题（这正是SQL擅长的），你需要一个表示时间增量的集合。这可以是一个临时表、一个永久表，或者一个派生表（也就是子查询）。我对SQLite不太熟悉，而且已经有一段时间没接触UNIX了。在UNIX中，时间戳就是从某个固定的日期/时间开始算起的秒数，对吧？使用一个标准的日历表（在数据库中有这个表是很有用的）……

SELECT
     C1.start_time,
     C2.end_time,
     T.org,
     COUNT(time)
FROM
     Calendar C1
INNER JOIN Calendar C2 ON
     C2.start_time = DATEADD(dy, 6, C1.start_time)
INNER JOIN My_Table T ON
     T.time BETWEEN C1.start_time AND C2.end_time  -- You'll need to convert to timestamp here
WHERE
     DATEPART(dw, C1.start_time) = 1 AND    -- Basically, only get dates that are a Sunday or whatever other day starts your intervals
     C1.start_time BETWEEN @start_range_date AND @end_range_date  -- Period for which you're running the report
GROUP BY
     C1.start_time,
     C2.end_time,
     T.org

日历表的形式可以随你选择，所以你可以在里面使用UNIX时间戳作为开始时间和结束时间。你只需要提前填充这个表，包含你可能想用的所有日期，范围可以是任何你能想到的。即使是从1900年1月1日到9999年12月31日，这个表也不会太大。这个表在很多报告类型的查询中会很有用。

最后，这段代码是T-SQL，所以你可能需要把DATEPART和DATEADD转换成SQLite中相应的函数。

回答于 2025-04-15 由 Python大师

分享举报

创建一个表格，列出从时间起点（也就是1970年1月1日）以来的所有周，然后把这个表格和你的事件表连接起来。

CREATE TABLE Weeks (
  week INTEGER PRIMARY KEY
);

INSERT INTO Weeks (week) VALUES (200919); -- e.g. this week

SELECT w.week, e.org, COUNT(*)
FROM Events e JOIN Weeks w ON (w.week = strftime('%Y%W', e.time))
GROUP BY w.week, e.org;

一年只有52到53周。即使你把这个周的表格填满100年，表格的大小也还是很小。

回答于 2025-04-15 由 Python大师

分享举报

因为我对SQLite不太熟悉，我觉得这种方法应该适用于大多数数据库，因为它可以找到周数并减去偏移量。

SELECT org, ROUND(time/604800) - week_offset, COUNT(*)
FROM table
GROUP BY org, ROUND(time/604800) - week_offset

在Oracle数据库中，如果时间是一个日期类型的列，我会使用以下方法：

SELECT org, TO_CHAR(time, 'YYYY-IW'), COUNT(*)
FROM table
GROUP BY org, TO_CHAR(time, 'YYYY-IW')

SQLite可能也有类似的功能，可以让这样的查询看起来更简单易懂。

回答于 2025-04-15 由 Python大师

分享举报

如何对字段值的每个增量进行分组？

3 个回答

撰写回答