如何为现有多表关联SQL查询添加同公司活跃用户统计功能

现有四张数据表，建表及插入数据的SQL如下：

create table GROSS(DATE_ACT DATE, SUB_ID BIGINT, PP_ID BIGINT, CUSTOMER_TYPE VARCHAR(50), REACTIVATION int);

insert into GROSS values('2019-11-03', 234, 5, 'Business', 1);
insert into GROSS values('2018-09-02', 131, 8, 'Business', 0);
insert into GROSS values('2018-11-03', 98, 3, 'Private', 1);
insert into GROSS values('2021-12-15', 228, 5, 'Business', 0);

create table TARIFF(PP_ID INT, PP_NAME VARCHAR(100), SUB_ID INT, PP_START_DATE DATE, PP_END_DATE DATE);
insert into TARIFF values(3, 'PLAN_1', 98, '2021-05-03', '2021-06-03');
insert into TARIFF values(5, 'Business plan 3.0', 234, '2021-05-06', '2021-06-06');
insert into TARIFF values(8, 'Business plan 4.0', 131, '2021-05-10', '2021-06-10');
insert into TARIFF values(5, 'Бизнес-план 3.0', 228, '2021-12-15', '2999-01-01');

create table SERVICE(SERVICE_START_DATE DATE, SERVICE_STOP_DATE DATE, SUB_ID INT, SERVICE_NAME VARCHAR(100));
insert into SERVICE values('2021-05-07', '2021-06-07', 98, 'Unlimited Internet 2');
insert into SERVICE values('2021-05-07', '2021-06-07', 98, 'Internet');
insert into SERVICE values('2021-05-07', '2021-06-07', 98, 'Unlimited Internet 512');
insert into SERVICE values('2021-12-15', '2999-01-01', 228, 'Безлимитный Интернет 512');

create table SUBSCRIBER(MONTH DATE, COMPANY_NAME VARCHAR(100), SUB_ID INT, CUSTOMER_TYPE VARCHAR(100), STATUS INT, PP_TYPE_ID VARCHAR(50));
insert into SUBSCRIBER values('2022-01-06', 'A1', 98, 'Private', 1, 'Fixed');
insert into SUBSCRIBER values('2022-01-06', 'MTS', 234, 'Business', 1, 'Fixed');
insert into SUBSCRIBER values('2022-01-06', 'Life', 131, 'Business', 1, 'Fixed');
insert into SUBSCRIBER values('2022-01-01','MTS', 228, 'Business', 1, 'Voice');

已编写多表关联查询语句如下：

select GROSS.DATE_ACT, GROSS.SUB_ID, TARIFF.PP_NAME, SERVICE.SERVICE_NAME, SUBSCRIBER.COMPANY_NAME
from GROSS 
inner join TARIFF on GROSS.SUB_ID = TARIFF.SUB_ID 
inner join SERVICE on TARIFF.SUB_ID = SERVICE.SUB_ID 
inner join SUBSCRIBER on SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
where MONTH(GROSS.DATE_ACT) = 12
and GROSS.CUSTOMER_TYPE = 'Business'
and TARIFF.PP_NAME regexp 'Бизнес-план.+'
and GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
and SERVICE.SERVICE_NAME = 'Безлимитный интернет 512' or 'Безлимитный интернет 1' or 'Безлимитный интернет 2'
and YEAR(SERVICE.SERVICE_STOP_DATE) = 2999;

需求：在返回现有查询结果的基础上，额外统计当前用户所属公司中，SUBSCRIBER.STATUS=1的活跃用户总数。

1. 修正原查询的逻辑错误 #

原查询的WHERE子句中，SERVICE.SERVICE_NAME的条件写法错误，直接用OR会导致逻辑优先级混乱，应该改用IN匹配多个目标值，同时注意字符串大小写匹配（原数据中服务名称是Безлимитный Интернет 512，首字母大写）。

2. 添加公司活跃用户统计 #

可以通过关联子查询在SELECT语句中直接获取当前用户所属公司的活跃用户总数，这种方式逻辑清晰，兼容大多数数据库。

最终修改后的SQL如下：

select 
    GROSS.DATE_ACT, 
    GROSS.SUB_ID, 
    TARIFF.PP_NAME, 
    SERVICE.SERVICE_NAME, 
    SUBSCRIBER.COMPANY_NAME,
    -- 统计当前公司STATUS=1的活跃用户总数
    (SELECT COUNT(*) 
     FROM SUBSCRIBER s2 
     WHERE s2.COMPANY_NAME = SUBSCRIBER.COMPANY_NAME 
       AND s2.STATUS = 1) AS COMPANY_ACTIVE_USERS
from GROSS 
inner join TARIFF on GROSS.SUB_ID = TARIFF.SUB_ID 
inner join SERVICE on TARIFF.SUB_ID = SERVICE.SUB_ID 
inner join SUBSCRIBER on SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
where MONTH(GROSS.DATE_ACT) = 12
  and GROSS.CUSTOMER_TYPE = 'Business'
  and TARIFF.PP_NAME regexp 'Бизнес-план.+'
  and GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
  -- 修正服务名称的匹配逻辑
  and SERVICE.SERVICE_NAME IN ('Безлимитный Интернет 512', 'Безлимитный интернет 1', 'Безлимитный интернет 2')
  and YEAR(SERVICE.SERVICE_STOP_DATE) = 2999;

3. 可选优化：使用窗口函数 #

如果数据库支持窗口函数（如MySQL 8.0+、PostgreSQL等），可以用窗口函数替代子查询，大数据量下性能更优：

select 
    GROSS.DATE_ACT, 
    GROSS.SUB_ID, 
    TARIFF.PP_NAME, 
    SERVICE.SERVICE_NAME, 
    SUBSCRIBER.COMPANY_NAME,
    -- 窗口函数按公司分组统计活跃用户
    COUNT(*) OVER (PARTITION BY SUBSCRIBER.COMPANY_NAME) AS COMPANY_ACTIVE_USERS
from GROSS 
inner join TARIFF on GROSS.SUB_ID = TARIFF.SUB_ID 
inner join SERVICE on TARIFF.SUB_ID = SERVICE.SUB_ID 
inner join SUBSCRIBER on SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
-- 先筛选出所有活跃用户，再关联其他表
inner join (
    SELECT COMPANY_NAME, SUB_ID 
    FROM SUBSCRIBER 
    WHERE STATUS = 1
) s_active on SUBSCRIBER.SUB_ID = s_active.SUB_ID
where MONTH(GROSS.DATE_ACT) = 12
  and GROSS.CUSTOMER_TYPE = 'Business'
  and TARIFF.PP_NAME regexp 'Бизнес-план.+'
  and GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
  and SERVICE.SERVICE_NAME IN ('Безлимитный Интернет 512', 'Безлимитный интернет 1', 'Безлимитный интернет 2')
  and YEAR(SERVICE.SERVICE_STOP_DATE) = 2999;

关键说明 #

• 子查询方式兼容性更好，适用于所有支持SQL的数据库；窗口函数方式在大数据量下性能更优。
• 原查询中日期值缺少单引号，已在示例SQL中补充，避免语法错误。

内容的提问来源于stack exchange，提问作者Matvey Androsyuk