存储架构

SQL Check if the values ​​in the disaggregate column do not match

微信扫一扫,分享到朋友圈

SQL Check if the values ​​in the disaggregate column do not match
0

I have a sql table with following values

|  col1 | col2|   source |  values
       |  1    |  2  |  A       |   null
       |  1    |  2  |  B       |   1.0
       |  1    |  2  |  C       |   null
       |  1    |  4  |  A       |   2.0
       |  1    |  4  |  B       |   2.0
       |  1    |  4  |  C       |   2.0
       |  1    |  5  |  A       |   null
       |  1    |  5  |  B       |   null
       |  1    |  5  |  C       |   null

How can I get an output with a group by of col1
and col2
with a flag:

  • all values match for a group ( flag = 1)
  • all values are null ( flag = 2)
  • some values is null (flag = 3)

Output:

|  col1 | col2|   flag
       |  1    |  2  |   3
       |  1    |  4  |   1
       |  1    |  5  |   2

Or:based on your updated question:

SELECT
    col1,
    col2,
    SUM(CASE WHEN SomeConditionHere THEN 1 ELSE 0 END) AS Flag
  FROM Table1
  GROUP BY col1, col2;


SQL Fiddle Demo

This will give you:

| COL1 | COL2 | FLAG |
----------------------
|    1 |    2 |    2 |
|    1 |    4 |    0 |
|    1 |    5 |    3 |

Note that:I assumed that the flag is how many NULL
values are in the VALUES
column, so I used "Values" IS NULL
instead of SomeConditionHere
.

I couldn’t understand how the flag
should be computed in the expected results you posted. You have to use the predicate that define your flag instead of "Values" IS NULL
.

Update:

Try this:

WITH Flags
AS
(
  SELECT
    col1, col2,
    COUNT(*) ValuesCount,
    SUM(CASE WHEN "Values" IS NULL THEN 1 ELSE 0 END) AS NULLValues
  FROM Table1
  GROUP BY col1, col2
)
SELECT
  col1,
  col2,
  Flag = CASE WHEN ValuesCount = NULLValues THEN 2
              WHEN NULLVALUES = 0
               AND ValuesCount = (SELECT COUNT(*)
                                  FROM Table1 t2
                                  WHERE t1.col1 = t2.col1
                                    AND t1.col2 = t2.col2) THEN 1
               ELSE 3
END
FROM Flags t1;


Updated SQL Fiddle Demo

This will give you:

| COL1 | COL2 | FLAG |
----------------------
|    1 |    2 |    3 |
|    1 |    4 |    1 |
|    1 |    5 |    2 |

阅读原文...


Hello, buddy!

Billionaire Elon Musk's $40 Million Tweet May Be A Blessing For Tesla After Settling Fraud Suit

上一篇

Google Pays Apple Billions of Dollars to Be Safari’s Default Search Engine

下一篇

您也可能喜欢

评论已经被关闭。

插入图片
SQL Check if the values ​​in the disaggregate column do not match

长按储存图像,分享给朋友