时间戳

首页 > 解决方案 > 在 MySQL 8 中使用窗口函数获取不同列的计数

mysql - 在 MySQL 8 中使用窗口函数获取不同列的计数

问题描述

我有一个 MVP DB 小提琴：https ://www.db-fiddle.com/f/cUn1Lo2xhbTAUwwV5q9wKV/2

我正在尝试shift_id使用窗口函数在任何日期获取表中唯一 s 的数量。

我尝试使用COUNT(DISTINCT(shift_id))，但目前在带有窗口函数的 MySQL 8 上不支持。

以防万一小提琴掉线。这是测试架构：

CREATE TABLE `scores` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `shift_id` int unsigned NOT NULL,
  `employee_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `score` double(8,2) unsigned NOT NULL,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO scores(shift_id, employee_name, score, created_at) VALUES
(1, "John", 6.72, "2020-04-01 00:00:00"),
(1, "Bob", 15.71, "2020-04-01 00:00:00"),
(1, "Bob", 54.02, "2020-04-01 00:00:00"),
(1, "John", 23.55, "2020-04-01 00:00:00"),

(2, "John", 9.13, "2020-04-02 00:00:00"),
(2, "Bob", 44.76, "2020-04-02 00:00:00"),
(2, "Bob", 33.40, "2020-04-02 00:00:00"),
(2, "James", 20, "2020-04-02 00:00:00"),

(3, "John", 20, "2020-04-02 00:00:00"),
(3, "Bob", 20, "2020-04-02 08:00:00"),
(3, "Bob", 30, "2020-04-02 08:00:00"),
(3, "James", 10, "2020-04-02 08:00:00")

我的查询有两种尝试方法，使用我在这篇文章中看到的：Count distinct in window functions

SELECT
    ANY_VALUE(employee_name) AS `employee_name`,
    DATE(created_at) AS `shift_date`,
    COUNT(*) OVER (PARTITION BY ANY_VALUE(created_at), ANY_VALUE(shift_id)) AS `shifts_on_day_1`,

    (
        dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) asc) +
        dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) desc) - 1
    ) as `shifts_on_day_2`

FROM scores
    GROUP BY employee_name, DATE(created_at);

预期结果将是日期为 2020-04-01 的任何行的 ashifts_on_day为 1，而日期为 4 月 2 日的行的ashifts_on_day为 2。

我考虑过使用相关子查询，但这是一个性能噩梦，表中有数百万行，查询中返回数千行。

更新：我认为窗口函数的必要性是查询中已经有一个 group by。一个查询中需要所有数据，最终目标是获取每个员工在特定日期的平均得分。为了得到每个员工的总分，我可以COUNT(*)。但是我需要将其除以一天中的总班次以获得平均值。

更新

最终结果是能够获得表中每个员工每个日期的总行数除以该日期发生的错误总数 - 这将提供该日期每个员工的平均行数。

因此，预期结果是：

姓名 | 班次日期 | 平均
------+------------+------
鲍勃 | 2020-04-01 | 2 2 / 1 = 2 ; Bob 的两行，一个 shift_id (1) 那天
鲍勃 | 2020-04-02 | 2 4 / 2 = 2 ; Bob 的四行，当天有两个 shift_ids (2,3)
詹姆斯 | 2020-04-02 | 1 2 / 2 = 1 ; 詹姆斯两行，那天两个 shift_ids (2,3)
约翰 | 2020-04-01 | 2 2 / 1 = 2 ; John 的两行，一个 shift_id (1) 那天
约翰 | 2020-04-02 | 1 2 / 2 = 1 ; 约翰两行，那天两个 shift_ids (2,3)

标签： mysqlsqlwindow-functionsmysql-8.0

解决方案

“每个日期和员工的所有行”和“每个日期的不同 ID 计数”是两个完全不同的聚合；您不能进行一个聚合并以某种方式从 elsewise 聚合行中检索另一个聚合。这规则聚合结果的窗口函数。

您需要两个单独的聚合。例如：

with empdays as
(
  select employee_name, date(created_at) as shift_date, count(*) as total
  from scores
  group by employee_name, date(created_at)
)
, days as 
(
  select date(created_at) as shift_date, count(distinct shift_id) as total
  from scores
  group by date(created_at)
)
select ed.employee_name, shift_date, ed.total / d.total as average
from empdays ed
join days d using (shift_date)
order by ed.employee_name, shift_date;

演示：https ://www.db-fiddle.com/f/qjqbibriXtos6Hsi5qcwi6/0

推荐阅读