如何在Redshift（Looker）中计算任务创建到完成的平均天数

核心查询（整体平均值） #

SELECT AVG(DATEDIFF(day, created, completed)) AS average_days_to_complete
FROM dbt_dw.base_package
WHERE 
  fk_company_id = 245821
  AND created >= '2022-10-01' 
  AND created < '2022-10-22'
  AND completed IS NOT NULL -- 仅统计已完成的任务

关键说明 #

• DATEDIFF(day, created, completed)：Redshift内置函数，计算两个日期时间字段的天数差，得到单个任务的耗时天数。
• AVG()：对所有符合条件的任务耗时取平均值。
• completed IS NOT NULL：过滤未完成的任务，避免无效数据影响结果准确性。

按日分组统计（含每日创建数+当日任务平均耗时） #

如果需要保留原有的每日创建数统计，同时查看当日创建任务的平均耗时，可使用以下查询：

SELECT 
  created::date AS creation_date,
  COUNT(n1pk_package_id) AS total_created,
  AVG(DATEDIFF(day, created, completed)) AS avg_days_to_complete
FROM dbt_dw.base_package
WHERE 
  fk_company_id = 245821
  AND created >= '2022-10-01' 
  AND created < '2022-10-22'
  AND completed IS NOT NULL
GROUP BY created::date
ORDER BY creation_date DESC

内容的提问来源于stack exchange，提问作者Gabriel Rocha