mysql - 如何在没有嵌套查询的情况下在 MySQL 中执行组聚合？

Question

我的表是这样的:

CREATE TABLE USER_TRANSACTIONS (
  START_TIME        BIGINT UNSIGNED  NOT NULL,
  APPLICATION_ID    CHAR(64) BINARY  NOT NULL,
  ENTRY_POINT       CHAR(255) BINARY NOT NULL,
  USER_ID           CHAR(64) BINARY  NOT NULL,
  ERROR_VIOLATION   BIT(1) NOT NULL,
  LATENCY_VIOLATION BIT(1) NOT NULL,
  PRIMARY KEY (START_TIME, APPLICATION_ID, ENTRY_POINT, USER_ID)
)

我想要实现的是如下摘要:我想了解每个入口点有多少唯一用户，以及其中有多少用户有错误和延迟问题。

例如:

ENTRY_POINT | TOTAL_USERS | TOTAL_ERRORS | TOTAL_LATENCY
page1       | 2           | 2            | 1
page2       | 1           | 1            | 1

我可以通过这个查询实现这个目标:

SELECT UT.ENTRY_POINT, COUNT(USER_ID) AS TOTAL_USERS, SUM(EXP_ERRORS) AS TOTAL_ERRORS, SUM(EXP_LATENCY) AS TOTAL_LATENCY
FROM (
    SELECT ENTRY_POINT, USER_ID,
    BIT_OR(ERROR_VIOLATION) AS EXP_ERRORS,
    BIT_OR(LATENCY_VIOLATION) AS EXP_LATENCY
    FROM user_transactions
    GROUP BY ENTRY_POINT, USER_ID
) AS UT
GROUP BY UT.ENTRY_POINT;

嵌套查询用于总结用户是否遇到错误或延迟问题，但在包含大量数据的表上我遇到了性能问题。

我的问题是如何优化此查询以避免使用内部子查询？

Answer 1

使用count(distinct)。这是编写查询的一种方法:

SELECT ENTRY_POINT, COUNT(DISTINCT USER_ID),
       SUM(ERROR_VIOLATION > 0) AS TOTAL_ERRORS,
       SUM(LATENCY_VIOLATION > 0) AS TOTAL_LATENCY
FROM user_transactions
GROUP BY ENTRY_POINT;

如果您希望用户有错误而不是总错误:

SELECT ENTRY_POINT, COUNT(DISTINCT USER_ID),
       COUNT(DISTINCT CASE WHEN ERROR_VIOLATION > 0 THEN USER_ID END) AS TOTAL_ERRORS,
       COUNT(DISTINCT CASE WHEN LATENCY_VIOLATION > 0 THEN USER_ID END) AS TOTAL_LATENCY
FROM user_transactions
GROUP BY ENTRY_POINT;