SQL Server:如何做 3 个月的分区? [英] SQL Server: how to do 3 months' partition?

问题描述

我想统计分区0-3个月的行数.月份由 MYMONTH 以 201601 的格式指定为 2016 年 1 月.我使用的是 SQL Server 2014.我如何做超过 3 个月的分区?

I want to count the number of rows in the partition 0-3months. Months are specified by MYMONTH in the format such that 201601 for 2016 January. I am using SQL Server 2014. How can I do the partition over 3 months?

SELECT  COUNT(*), 
        COUNT(*)
           /  
        (COUNT(*) OVER (PARTITION 
            BY MYMONTH RANGE BETWEEN  3 MONTH PRECEDING AND CURRENT MONTH))
FROM myData

示例

|  Month  | Value  |  ID |
-------------------------|
| 201601  |   1    |  X  |
| 201601  |   1    |  Y  |
| 201601  |   1    |  Y  |
| 201602  |   1    |  Z  |
| 201603  |   1    |  A  |
| 201604  |   1    |  B  |
| 201605  |   1    |  C  |
| 201607  |   1    |  E  |
| 201607  |   10   |  EE |
| 201607  |   100  |  EEE|

计数

| Month | Count | Count3M | Count/Count3M |
-------------------------------------------
| 201601|   3   |    3    |     3/3       |
| 201602|   1   |    4    |     1/4       |
| 201603|   1   |    5    |     1/5       |
| 201604|   1   |    6    |     1/6       |
| 201605|   1   |    4    |     1/4       |
| 201607|   3   |    5    |     3/5       |

推荐答案

你可以试试这个(MSSQL 2012):

You can try this (MSSQL 2012):

样本数据

CREATE TABLE mytable(
   MONT INTEGER  NOT NULL  
  ,Value INTEGER  NOT NULL
  ,ID    VARCHAR(5) NOT NULL
);
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'X');
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'Y');
INSERT INTO mytable(MONT,Value,ID) VALUES (201601,1,'Y');
INSERT INTO mytable(MONT,Value,ID) VALUES (201602,1,'Z');
INSERT INTO mytable(MONT,Value,ID) VALUES (201603,1,'A');
INSERT INTO mytable(MONT,Value,ID) VALUES (201604,1,'B');
INSERT INTO mytable(MONT,Value,ID) VALUES (201605,1,'C');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,1,'E');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,10,'EE');
INSERT INTO mytable(MONT,Value,ID) VALUES (201607,100,'EEE');

查询 1

SELECT MONT, RC,  RC+ LAG(RC,3,0)  OVER (  ORDER BY MONT)+ LAG(RC,2,0)  OVER (  ORDER BY MONT)  + LAG(RC,1,0)  OVER (  ORDER BY MONT)   AS RC_3M_PREC -- + COALESCE( LEAD(RC)  OVER (  ORDER BY MONT),0) AS RC_3M
FROM (SELECT MONT
    , COUNT(*) RC
     FROM mytable
     GROUP BY MONT
 ) A

输出:

MONT        RC          RC_3M_PREC
----------- ----------- -----------
201601      3           3
201602      1           4
201603      1           5
201604      1           6
201605      1           4
201607      3           6

或者使用你提议的(选项 ROWS ... PRECEDING):

查询 2:

SELECT MONT, RC
    ,   COALESCE(SUM(RC)   OVER (ORDER BY MONT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),0) AS RC_3M
FROM (SELECT MONT
    , COUNT(*) RC    
FROM mytable
GROUP BY MONT
 ) A

输出:

MONT        RC          RC_3M
----------- ----------- -----------
201601      3           3
201602      1           4
201603      1           5
201604      1           6
201605      1           4
201607      3           6

这篇关于SQL Server:如何做 3 个月的分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！