SQL特定时间范围数据查询：Stats表统计需求及报错求助

首先，你的原代码存在几个核心问题：

• 基础查询逻辑混乱，子查询返回多列却用=比较，且误用DISTINCT COUNT语法
• 存储过程的参数定义不符合PL/pgSQL规范，时间范围的过滤条件逻辑颠倒，且不必要拆分日期和时间字段
• 表结构设计有缺陷：employee_id作为主键会限制同一员工多次搜索的记录插入

修正后的表结构 #

先调整表结构，避免主键冲突问题：

CREATE TABLE Stats (
  id SERIAL PRIMARY KEY, -- 新增自增主键，支持同一员工多次搜索
  employee_id INT,
  number_of_employees INT,
  search VARCHAR(50),
  number_of_results INT,
  start_time TIMESTAMPTZ, -- 保留带时区的时间类型
  completion_time TIMESTAMPTZ
);

-- 插入测试数据（修正引号为英文双引号）
INSERT INTO Stats (employee_id, number_of_employees, search, number_of_results, start_time, completion_time)
VALUES 
(001, 125, 'organization', 2000, '2020-04-18 12:13:21.101+00', '2020-04-18 12:13:26.53+00'),
(005, 127, 'organization', 2000, '2020-04-18 12:14:32.877+00', '2020-04-18 12:16:53.43+00');

核心查询实现（单SQL获取所有统计值） #

直接用一条SQL即可满足三个需求，无需复杂存储过程：

SELECT
  COUNT(DISTINCT employee_id) AS unique_employee_count, -- 1. 特定时间范围内的不同员工数
  COUNT(*) AS total_search_count, -- 2. 特定时间范围内的搜索总次数
  -- 3. 平均搜索时长（转换为秒，保留两位小数）
  ROUND(AVG(EXTRACT(EPOCH FROM (completion_time - start_time))), 2) AS avg_search_duration_seconds
FROM Stats
-- 替换为你需要的时间范围
WHERE start_time >= '2020-04-18 00:00:00+00'
  AND completion_time <= '2020-04-19 00:00:00+00';

关键语法说明 #

• 时间差计算：PostgreSQL中两个TIMESTAMPTZ类型直接相减得到INTERVAL类型，用EXTRACT(EPOCH FROM interval)将其转换为秒数（数值型），方便计算平均值
• 不同员工数：COUNT(DISTINCT employee_id)自动去重统计唯一员工ID
• 搜索次数：每一行对应一次搜索操作，用COUNT(*)统计总行数即可

封装为存储过程（可选） #

如果需要复用逻辑，可以封装为带参数的存储过程：

CREATE OR REPLACE PROCEDURE get_search_stats(
  IN p_start_time TIMESTAMPTZ, -- 输入起始时间
  IN p_end_time TIMESTAMPTZ, -- 输入结束时间
  OUT p_unique_employees INT, -- 输出：不同员工数
  OUT p_total_searches INT, -- 输出：搜索总次数
  OUT p_avg_duration_sec NUMERIC -- 输出：平均搜索时长（秒）
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT
    COUNT(DISTINCT employee_id),
    COUNT(*),
    ROUND(AVG(EXTRACT(EPOCH FROM (completion_time - start_time))), 2)
  INTO p_unique_employees, p_total_searches, p_avg_duration_sec
  FROM Stats
  WHERE start_time >= p_start_time
    AND completion_time <= p_end_time;
END;
$$;

调用存储过程 #

-- 在psql中调用，直接获取输出结果
CALL get_search_stats('2020-04-18 00:00:00+00', '2020-04-19 00:00:00+00', @unique, @total, @avg);
SELECT @unique, @total, @avg;

内容的提问来源于stack exchange，提问作者Chimmy Wang