正文

SQL Server ->> 间接实现COUNT(DISTINCT XXX) OVER(PARTITION BY YYY)

IP云
IP云V管理员/3 阅读
11/28
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

SQL Server 2005版本开始支持了窗口函数（Windowing Function）和OVER字句。SQL Server 2012版本开始支持了窗口函数的ORDER BY字句实现连续/累计聚合功能。但是有个功能到SQL Server 2014版本为止（从目前SQL Server 2016 CTP3来看，还是不支持），就是COUNT(DISTINCT XXX) OVER(PARTITION BY YYY)。

一直觉得这个事情没有办法用比较巧妙地办法做到，只能是用CROSS APPLY或者循环去把数据集“UNION ALL”起来。今天在Google上找到了一个别人想到的办法，顿时觉得自己怎么那么笨，没有想到这个办法呢。巧妙之处在于用一个带有DENSE_RANK函数的子查询配合MAX函数实现。下面是测试代码： 

CREATE TABLE Foo (

fookey INT PRIMARY KEY,

company VARCHAR(30),

sales_rep VARCHAR(30),

client VARCHAR(30));

INSERT INTO Foo VALUES(1, 'ABC Corp.', 'Joe', 'Client1');

INSERT INTO Foo VALUES(2, 'ABC Corp.', 'Joe', 'Client2');

INSERT INTO Foo VALUES(6, 'ABC Corp.', 'Joe', 'Client2');

INSERT INTO Foo VALUES(3, 'ABC Corp.', 'Peter', 'Client2');

INSERT INTO Foo VALUES(4, 'DEF Corp.', 'Joe', 'Client1');

INSERT INTO Foo VALUES(5, 'DEF Corp.', 'Joe', 'Client3');

SELECT DISTINCT company, sales_rep, client,

MAX(rk1) OVER(PARTITION BY sales_rep) AS rep_distinct_client_cnt,

MAX(rk2) OVER(PARTITION BY company) AS company_distinct_client_cnt

FROM (

SELECT fookey, company, sales_rep, client,

DENSE_RANK() OVER(PARTITION BY sales_rep ORDER BY client) As rk1,

DENSE_RANK() OVER(PARTITION BY company ORDER BY client) As rk2

FROM Foo) AS F;

这个办法精妙的地方便是利用了DENSE_RANK本身会对相同值返回相同的排序号的特点，这点恰恰符合了我们需要DISTINCT的作用。其次，排序号和COUNT的相同之处不就是对记录的个数统计吗？那么取得最大的排序号不就相当于拿到了COUNT的值了吗？确实高明。感叹自己过去怎么就没想到呢？还是很开心可以知道这个办法的。

SQL Server ->> 间接实现COUNT(DISTINCT XXX) OVER(PARTITION BY YYY)的更多相关文章
  1. Change the Target Recovery Time of a Database (SQL Server) 间接-checkpoints flushcache flushcache-message

    Change the Target Recovery Time of a Database (SQL Server) 间接checkpoints   flushcache flushcache-mes ...

  2. sql server 2005中的分区函数用法(partition by 字段)

    分组取TOP数据是T-SQL中的常用查询, 如学生信息管理系统中取出每个学科前3名的学生.这种查询在SQL Server 2005之前,写起来很繁琐,需要用到临时表关联查询才能取到.SQL Serve ...

  3. sql server 学习笔记 ( row_number, rank, dense_rank over partition by order by )

    refer : https://blog.csdn.net/winer2008/article/details/4283539 https://www.cnblogs.com/linJie193090 ...

  4. SQL Server开窗函数之OVER子句、PARTITION BY 子句

    开窗函数与聚合函数一样,都是对行的集合组进行聚合计算.它用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同 ...

  5. Sql server 表表达式

    1.表表达式概述 (1)表表达式(table expression) 是一个命名的查询表达式.代表一个有效的关系表 (2)在DML 中,使用表表达式和使用其他表非常类似 (3)sqlserver 支持 ...

  6. 数据库开发基础-SQl Server 聚合函数、数学函数、字符串函数、时间日期函数

    SQL 拥有很多可用于计数和计算的内建函数. 函数的语法 内建 SQL 函数的语法是: SELECT function(列) FROM 表 函数的类型 在 SQL 中,基本的函数类型和种类有若干种.函 ...

  7. SQL Server 子查询错误：No column name was specified for column 2 of 'a' error （转载）

    问: I have a MySQL query and I ran it working fine but same query showing error in SQL Server. SQL Se ...

  8. 同样的一句SQL语句在pl/sql 代码块中count 没有数据，但是直接用SQl 执行却可以count 得到结果

    pl/sql 代码块: SELECT count(distinct t2.so_nbr) INTO v_count2 FROM KFGL_YW_STEP_qd t2 WHERE t2.partitio ...

  9. SQL Server遗失管理权限账号密码怎么办？

    假如一个SQL Server实例只允许"SQL身份认证"模式登录数据库,而糟糕的是你忘记了sa的密码(sa出于安全考虑应该被禁用,这里仅仅为了描述问题)或其它具有sysadmin角 ...

随机推荐
  1. webpack 打包之后，两行溢出没有效果

    原因:发现-webkit-box-orient:vertical;并未设置成功解决:-webkit-box-orient: vertical; 加上注释包裹 .item-title { overflo ...

  2. js控制a标签点击事件 触发下载

    问题背景,动态获取data把url赋值到a标签的url中,让a标签自动下载 首先想到的应该是$('xxx').click(), 查资料明白:js中的$(...).click()事件只能触发绑定的onC ...

  3. selenium+junit4实现参数化自动化测试

    业务场景:在www.1905.com电影网中实现两个用户的登陆操作. 代码如下: package com.m1905.junit; import java.util.Arrays; import ja ...

  4. SQL Cookbook—插入、更新与删除

    涉及到的问题–1.从一个表向另外的表中复制行–2.复制表定义(包含表记录)–3.一次向多个表中插入记录–4.–5.当相应行存在时更新–6.用其他表中的值更新–7.删除违反参照完整性的记录 –1.从一个 ...

  5. [PY3]——根据某个特定的字段来分组迭代访问一个字段或序列？/ itertools.groupby( )函数

    问题 你有一个字典或者实例的序列,然后你想根据某个特定的字段(比如‘date’)来分组迭代访问. 解决方案 itertools.groupby( )函数 itertools.groupby(rows, ...

  6. java.lang.RuntimeException Unable to instantiate application Caused by: java

    参考:http://blog.csdn.net/xufazhong/article/details/71155528 dependencies { classpath 'com.android.too ...

  7. solr linux配置

    一.先在官网下载solr的最新版本或者你需求的版本1 目前我使用的是4.10.3(http://archive.apache.org/dist/lucene/solr/4.10.3/)2 复制到你的云 ...

  8. Nginx通关攻略

    Nginx是什么 没有听过Nginx?不要紧,一定听过它的"同行"Apache吧!Nginx同Apache一样都是一种WEB服务器.基于REST架构风格,以统一资源描述符(Unif ...

  9. 《Maven实战》关联实际工作的核心知识

    通读了<Maven实战>这本书,由于在实际的工作中,对其有一定的操作上的经验.因此,再回头去通读这本书,就能够更加精准的把握里面的核心知识了. 以下我主要从两点去介绍之—— 1> m ...

  10. 虚拟机下centos时间不正确的方便解决方法

    就是用NTP了,通过外部的服务同步时间. ntpdate us.pool.ntp.org | logger -t NTP 如果没有ntpdate ,可以使用 yum install ntpdate 进 ...

相关阅读

目录[+]