时间戳

首页 > 解决方案 > 有没有更好的方法来计算小时工和销售额？

sql - 有没有更好的方法来计算小时工和销售额？

问题描述

我有一个当前有效但基于效率低下的查询。我基本上是在尝试按小时对劳动力和销售数据进行分组。

我希望能够通过一天的查询来做到这一点。

我正在使用 PostgreSQL。

我有一堆带有 的打卡记录，employee_id但是如果员工已经打卡但没有打卡job_id，location_id我必须检查 clock_out_time 字段并将其设置now()为正确地进行每小时计算。

规划时间：0.509 ms

执行时间：0.498 ms

我用 30-50 条记录来做这件事，所以这不会扩展。

我能做些什么来改善这一点？

SELECT
  date_trunc('hour', tp.clock_in_time) AS hour,
  SUM(
    (
      EXTRACT (DAY FROM (CASE WHEN EXTRACT(YEAR FROM tp.clock_out_time) = -1 THEN now() ELSE tp.clock_out_time END - tp.clock_in_time))*24*60*60+
      EXTRACT (HOUR FROM (CASE WHEN EXTRACT(YEAR FROM tp.clock_out_time) = -1 THEN now() ELSE tp.clock_out_time END - tp.clock_in_time))*60*60+
      EXTRACT (MINUTE FROM (CASE WHEN EXTRACT(YEAR FROM tp.clock_out_time) = -1 THEN now() ELSE tp.clock_out_time END - tp.clock_in_time))*60+
      EXTRACT (SECOND FROM (CASE WHEN EXTRACT(YEAR FROM tp.clock_out_time) = -1 THEN now() ELSE tp.clock_out_time END - tp.clock_in_time))
    ) / 60 / 60.00 * (job.rate / 100.00)
  ) AS labor_costs,
  (
  SELECT 
    SUM(total) / 100.00
    FROM 
        ticket
    WHERE 
        open=false 
    AND 
        DATE_TRUNC('day', opened_at) = date_trunc('day', '2018-12-22T11:15:05-05:00'::date) 
    AND
      DATE_TRUNC('day', closed_at) = date_trunc('day', '2018-12-22T11:15:05-05:00'::date) 
    GROUP BY date_trunc('hour', opened_at) 
    ORDER BY date_trunc('hour', opened_at)
    ) AS hourly_sales
FROM 
  employee_time_punch as tp
INNER JOIN
  employee
ON 
  employee.id = tp.employee_id
INNER JOIN
  employee_job as job
ON
  job.id = tp.job_id
WHERE
  DATE_TRUNC('day', tp.clock_in_time) = DATE_TRUNC('day', '2006-01-02T11:15:05-05:00'::date)
AND
    DATE_TRUNC('day', CASE WHEN EXTRACT(YEAR FROM tp.clock_out_time) = -1 THEN now() ELSE tp.clock_out_time END) = DATE_TRUNC('day', '2006-01-02T11:15:05-05:00'::date)
GROUP BY 1
ORDER BY 1;

标签： sqlpostgresql

解决方案

WHERE DATE_TRUNC('day', tp.clock_in_time) = DATE_TRUNC('day', '2006-01-02T11:15:05-05:00'::date)

这种单一的过滤条件正在损害您的查询。它患有“平等中的左侧表达”综合症，无法使用任何索引。此时 PostgreSQL 可能正在对表执行全表扫描。

如果您将条件改写为如下所示，则可以使查询速度更快：

WHERE tp.clock_in_time BETWEEN ...begin_of_day... AND ...end_of_day...

您可以根据需要预先计算 CTE 中的这些值。

而且——当然——你需要在列上有一个索引，如下所示：

create index ix1 on employee_time_punch (clock_in_time);

有了这个更改，PostgreSQL 将改为执行索引范围扫描，速度更快。

推荐阅读