TMilliman's questions

考虑下表：

CREATE TABLE T1
(
  keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
  (2, 'A'),(3, 'A'),
  (5, 'B'),(7, 'B'),(11, 'B'),
  (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');

目前，我正在研究窗口函数并尝试聚合窗口函数。OVER虽然我觉得我了解如何使用and子句定义窗口PARITION，但我不确定如何计算聚合窗口函数，例如AVG() OVER ().

我希望了解以下三个查询。

-- Query 1
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg 
FROM T1 

关键点 | col1 | 行平均
-----: | :--- | -----：
     2 | 一个 | 2
     3 | 一个 | 2
     5 | 乙| 7
     7 | 乙| 7
    11 | 乙| 7
    13 | C | 18
    17 | C | 18
    19 | C | 18
    23 | C | 18

-- Query 2
SELECT keycol, col1, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1 

关键点 | col1 | 行平均
-----: | :--- | -----：
     2 | 一个 | 2
     3 | 一个 | 2
     5 | 乙| 3
     7 | 乙| 4
    11 | 乙| 5
    13 | C | 6
    17 | C | 8
    19 | C | 9
    23 | C | 11

-- Query 3
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1 

关键点 | col1 | 行平均
-----: | :--- | -----：
     2 | 一个 | 2
     3 | 一个 | 2
     5 | 乙| 5
     7 | 乙| 6
    11 | 乙| 7
    13 | C | 13
    17 | C | 15
    19 | C | 16
    23 | C | 18

查询 1：我相信 RowAvg 应该是每个 col1 级别的行的平均值。数字 2 和 7 是平均数还是我的理解不正确？

查询 2：我不太确定在这里生成 RowAvg 正在做什么。由于这里没有使用 PARTITION 或框架，我认为窗口应该是整个表，这是正确的吗？另外，如何找到 RowAvg？

查询 3：这是否找到了每个分区的 (FLOOR) 平均值，但是这样做是增量的？也就是说，对于第一个分区 ('A') 的第 1 行，我们找到该行的平均值。然后，对于第一个分区的第 2 行，我们找到前 2 行的平均值。

一般问题：ORDER BY在聚合窗口函数中引入是否“连续”执行聚合函数，例如在查询 1 和 2 中？有趣的是，在查询 1 中，AVG对每个分区作为一个整体执行，而在查询 1 和 2 中，每一行的 RowAvg 几乎不同。

考虑下表，

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);

ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);

INSERT INTO GapsIslands
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 5 UNION ALL SELECT 1, 6 
UNION ALL SELECT 1, 8 UNION ALL SELECT 1, 9 UNION ALL SELECT 1, 10 UNION ALL SELECT 1, 12 
UNION ALL SELECT 1, 20 UNION ALL SELECT 1, 21 UNION ALL SELECT 1, 25 UNION ALL SELECT 1, 26;  

我正在努力理解NOT EXISTS在差距和岛屿解决方案的背景下条件的应用。

以下查询旨在查找“岛屿”的起点：

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS ( 
    SELECT  * 
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)

现在在 NOT EXISTS 条件下的查询，

SELECT  * 
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1

返回，列（让我们将此列称为 A）SeqNo 为：

 SeqNo      
  1     
  5     
  8     
  9     
  20        
  25        

SeqNo 的“完整”列（我们称之为 B 列）是：

  SeqNo     
  1     
  2     
  5     
  6     
  8     
  9
  10        
  12        
  20        
  21        
  25
  26

那么，为什么当我们在 A 列中取 B 列值 WHERE NOT EXISTS 时，我们会找到该列吗？

 SeqNo      
  1     
  5     
  8     
  12        
  20        
  25

B列的值1也是IN列A，那么为什么值1会出现在查询的SeqNo列中呢？

考虑下表

CREATE TABLE Main (
    id INT PRIMARY KEY,
    state VARCHAR(255) DEFAULT NULL 
)

CREATE TABLE ColorTable (
    color VARCHAR(255),
    state  VARCHAR(255)
)


INSERT INTO Main (id, state) VALUES 
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'B'),
    (5, 'C'),
    (6, 'A'),
    (7, NULL)

INSERT INTO ColorTable (color, state) VALUES 
    ('Yellow', 'A'),
    ('Red', 'B'),
    ('Green', 'C'),
    ('Black', NULL)

我想在“主”表中添加一个新列“颜色”。我希望每个 id 的颜色都基于“Main”中的状态和“ColorTable”中的相应颜色。

我可以通过以下方式实现这一点：

ALTER TABLE main  
ADD color VARCHAR(255)

UPDATE main 
SET main.color = ColorTable.color 
FROM main 
LEFT JOIN ColorTable ON main.state = ColorTable.state 

UPDATE main 
SET color = 'Red'
WHERE state IS NULL 

我很好奇这样做的替代方法——我可以使用 SELECT INTO 或其他方法获得相同的结果吗？

考虑下表，

CREATE TABLE temp ( 
    [TabName] VARCHAR(255),
    [ID] VARCHAR(255),
    [AsOfDate] DATE)

  INSERT INTO temp VALUES 
    ('TAB1', 'C103', '2019-05-01'),
    ('TAB1', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-06-01'),
    ('TAB2', 'C103', '2019-07-01'),
    ('TAB1', 'C103', '2019-09-01'),
    ('TAB1', 'C104', '2019-06-01'),
    ('TAB1', 'C104', '2019-08-01')

TabName     ID      AsOfDate      
TAB1        C103     '2019-05-01'            
TAB1        C103     '2019-06-01'                        
TAB2        C103     '2019-06-01'                                    
TAB2        C103     '2019-07-01'     
TAB1        C103     '2019-09-01'
TAB1        C104     '2019-06-01'
TAB1        C104     '2019-08-01'
TAB1        C105     '2019-04-01'
TAB1        C105     '2019-05-01' 

我想从表中查找已跳过日期的 ID。在此表中，我想确定 ID C103 和 C104 已跳过日期，因为它们已从“2019-07-01”跳到“2019-09-01”和“2019-06-01”跳到“2019-8” -01'。

我发现之前提出的以下问题Find Missing Dates in Data我相信这为该方法提供了一些潜在的线索，即使用 CTE，但是，我不确定如何将它们应用于整个表中日期不连续的问题.

我们应该在这里查看分区吗？

考虑下表，

CREATE TABLE temp (
    [TabName] VARCHAR(255),
    [PersonID] VARCHAR(255),
    [FirstName] VARCHAR(255), 
    [Gender] VARCHAR(255),
    [BenefitType] VARCHAR(255),
    [HealthStatus] VARCHAR(255));

  INSERT INTO temp VALUES 
    ('TAB1', 'C103','John', 'M', 'Benefit Type 1', NULL),
    ('TAB2', 'C103','John', 'M', NULL, 'Healthy'),
    ('TAB3', 'C103', 'John', 'M', NULL, 'Healthy'),
    ('TAB1', 'C104', 'Mary', 'M', 'Benefit Type 2', NULL),
    ('TAB2', 'C104', 'Mary', 'M', NULL, 'Sick'),
    ('TAB3', 'C104', 'Mary', 'M', NULL, NULL)  

TabName     PersonID     FirstName    BenefitType      HealthStatus 
TAB1        C103         John         Benefit Type 1   NULL   
TAB2        C103         John         NULL             Healthy
TAB3        C103         John         NULL             Healthy
TAB1        C104         Mary         Benefit Type 2   NULL   
TAB2        C104         Mary         NULL             Sick   
TAB3        C104         Mary         NULL             NULL   

我想将跨 TabName 的信息合并到一个新表中，以便新表包含每个 PersonID 的所有唯一信息。

例如，根据上表，我想找到新的表

PersonID   FirstName    BenefitType      HealthStatus   
C103       John         Benefit Type 1   Healthy  
C104       Mary         Benefit Type 2   Sick   

我不太确定从哪里开始解决这个问题。

考虑下表

TabName     ID      Gender    FirstName 
Tab1        C1      M         John   
Tab2        C1      M         John   
Tab3        C1      F         John   
Tab1        C2      F         Mary  
Tab2        C2      F         Mary  
Tab3        C2      F         Mary  

我正在寻找识别“标签”之间存在不一致的个人 ID。例如，标签 'Tab1' 和 'Tab2' 中的 ID 'C1' 不同，但是，'Tab3' 中的 Gender 字段发生了变化。另一方面，“玛丽”在所有三个选项卡中都是一致的。

一种潜在的方法可能是找到“ID”，其中重复记录的计数（每行完全相同）不等于每个“ID”的出现次数。

但是，我不确定如何编写上述 SQL。此外，我认为可能有更好、更清洁的方法来解决这个问题。