sql - 由第三列分组的另一列上不同行内列 = A 的行的平均值

Question

使用 SQL Server，我试图从我没有设计的表中查询一种平均计数，基本上我想要一个列表，按一列分组，其中另一列的不同值的数量与给定的标准，以及其中的匹配另一个标准的行数(我将使用它来创建平均计数或其他任何值)。这并不难，但我今天的集合理论很糟糕，任何指点都将不胜感激。

这是简化和通用的场景(下面的架构和示例数据)。假设我们有三列:

• objid (有聚集索引)
• userid (没有索引，我也许可以添加一个)
• actiontype (没有索引，我也许可以添加一个)

这些都不是唯一的，也不能为 null。我们希望完全忽略 actiontype 为 none 的任何行。我们想知道，每个 userid，用户与之交互的每个对象平均有多少 actiontype = 'flag' 行。

因此，如果我们有“ahmed”、“joe”和“maria”，并且 joe 与 3 个物体交互并举起了 5 个旗帜，则那里的数字是 5/3 = 1.6666 连续的；如果“ahmed”与 3 个物体交互并且没有引发任何标志，那么他的编号将为0；如果“maria”与 5 个物体交互并举起了 4 个旗帜，那么她的数量将为 4/5 = 0.8:

+--------+------------------+| userid | flags_per_object |+--------+------------------+| ahmed  | 0                || joe    | 1.66666667       || maria  | 0.8              |+--------+------------------+

I won't be remotely surprised if this is closed as a duplicate, I'm just not finding it.

Here's the simplified table setup and sample data:

create table tmp
(
    objid      varchar(254) not null,
    userid     varchar(254) not null,
    actiontype varchar(254) not null
)
create clustered index tmp_objid on tmp(objid)

insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'none')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'none')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'update')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'close')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'flag')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'flag')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'flag')
insert into tmp (objid, userid, actiontype) values ('alpha', 'joe', 'flag')

insert into tmp (objid, userid, actiontype) values ('beta', 'joe', 'none')
insert into tmp (objid, userid, actiontype) values ('beta', 'joe', 'none')
insert into tmp (objid, userid, actiontype) values ('beta', 'joe', 'close')
insert into tmp (objid, userid, actiontype) values ('beta', 'joe', 'flag')

insert into tmp (objid, userid, actiontype) values ('gamma', 'joe', 'none')

insert into tmp (objid, userid, actiontype) values ('delta', 'joe', 'update')

insert into tmp (objid, userid, actiontype) values ('alpha', 'maria', 'update')

insert into tmp (objid, userid, actiontype) values ('beta', 'maria', 'flag')
insert into tmp (objid, userid, actiontype) values ('beta', 'maria', 'flag')

insert into tmp (objid, userid, actiontype) values ('gamma', 'maria', 'flag')
insert into tmp (objid, userid, actiontype) values ('gamma', 'maria', 'flag')
insert into tmp (objid, userid, actiontype) values ('gamma', 'maria', 'update')
insert into tmp (objid, userid, actiontype) values ('gamma', 'maria', 'close')

insert into tmp (objid, userid, actiontype) values ('delta', 'maria', 'update')
insert into tmp (objid, userid, actiontype) values ('epsilon', 'maria', 'update')

insert into tmp (objid, userid, actiontype) values ('alpha', 'ahmed', 'none')

insert into tmp (objid, userid, actiontype) values ('beta', 'ahmed', 'none')

insert into tmp (objid, userid, actiontype) values ('gamma', 'ahmed', 'none')
insert into tmp (objid, userid, actiontype) values ('gamma', 'ahmed', 'update')

insert into tmp (objid, userid, actiontype) values ('delta', 'ahmed', 'update')
insert into tmp (objid, userid, actiontype) values ('delta', 'ahmed', 'close')

insert into tmp (objid, userid, actiontype) values ('epsilon', 'ahmed', 'update')
insert into tmp (objid, userid, actiontype) values ('epsilon', 'ahmed', 'close')

Answer 1

您可以尝试以下操作:

select  t1.userid,
CASE cnt2 
WHEN 0 THEN 0
ELSE ISNULL(cast(cnt2 as float)/cnt1,0)
END as num
FROM
(
  select userid, COUNT(distinct(t1.objid)) as cnt2
  from tmp as t1
  where t1.actiontype <> 'none'
  group by t1.userid
) t1

LEFT JOIN (
SELECT t2.userid, COUNT(*) as cnt1
FROM tmp as t2
WHERE t2.actiontype='flag'
GROUP BY t2.userid)b ON (b.userid = t1.userid)

尽管它看起来比您的解决方案更难看，但令人惊讶的是，它根据您提供的测试数据生成了更好的执行计划。