Oracle SQL：将计数分为唯一列和非唯一列

发布于 2025-01-10 19:05:47 字数 599 浏览 1 评论 0原文

我有一个如下所示的表：

|FileID|  File Info   |
| ---- | ------------ |
|   1  | X            |
|   1  | Y            |
|   2  | Y            |
|   2  | Z            |
|   2  | A            |

我想按 FileID 进行聚合，并将“文件信息”列拆分为 2 个单独的计数列。我希望一列包含唯一文件信息的计数，另一列包含非唯一文件信息的计数。

理想情况下，结果如下所示：

|FileID| Count(Unique)| Count(Non-unique) |
| ---- | ------------ | ----------------- |
|   1  | 1            | 1                 |
|   2  | 2            | 1                 |

其中非唯一计数是“Y”，唯一计数分别来自 FileID 1 和 2 的“X”和“Z, A”。

我正在寻找方法来衡量文件之间而不是内部的唯一性。

原文

I have a table that looks like this:

|FileID|  File Info   |
| ---- | ------------ |
|   1  | X            |
|   1  | Y            |
|   2  | Y            |
|   2  | Z            |
|   2  | A            |

I want to aggregate by FileID and split the File Info column into 2 separate count columns. I want 1 column to have the count of the Unique File Info and the other to be a count of non-Unique file info.

The result would ideally look like this:

|FileID| Count(Unique)| Count(Non-unique) |
| ---- | ------------ | ----------------- |
|   1  | 1            | 1                 |
|   2  | 2            | 1                 |

where the non-unique count is the 'Y' and the unique count is from the 'X' and 'Z, A' for FileID 1 and 2 respectively.

I'm looking for ways to gauge uniqueness between files rather than within.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吻泪 2025-01-17 19:05:47

在每一行中使用 COUNT() 窗口函数检查 FileInfo 是否唯一，然后使用条件聚合来获取您想要的结果：

SELECT FileID,
       COUNT(CASE WHEN counter = 1 THEN 1 END) count_unique,
       COUNT(CASE WHEN counter > 1 THEN 1 END) count_non_unique
FROM (
  SELECT t.*, COUNT(*) OVER (PARTITION BY t.FileInfo) counter
  FROM tablename t
) t
GROUP BY FileID;

请参阅演示。

Use COUNT() window function in every row to check if FileInfo is unique and then use conditional aggregation to get the results that you want:

SELECT FileID,
       COUNT(CASE WHEN counter = 1 THEN 1 END) count_unique,
       COUNT(CASE WHEN counter > 1 THEN 1 END) count_non_unique
FROM (
  SELECT t.*, COUNT(*) OVER (PARTITION BY t.FileInfo) counter
  FROM tablename t
) t
GROUP BY FileID;

See the demo.

回复收藏 0 原文

雨巷深深 2025-01-17 19:05:47

首先，从表中选择“非唯一”行

SELECT FileInfo
FROM sometableyoudidnotname
GROUP BY FileInfo
HAVING COUNT(*) > 1

现在您知道哪些行是唯一的，哪些是非唯一的，您可以保留该表的连接以获取“状态”并对其进行计数。

SELECT base.FileID, 
       SUM(CASE WHEN u.FileID is NOT NULL THEN 1 ELSE 0 END) as nonunique,
       SUM(CASE WHEN u.FileID is NULL THEN 1 ELSE 0 END) as unique
FROM sometableyoudidnotname base
LEFT JOIN (
  SELECT FileInfo
  FROM sometableyoudidnotname
  GROUP BY FileInfo
  HAVING COUNT(*) > 1
) u ON base.FileInfo = u.FileInfo
GROUP BY base.FileID

First you select the "Non Unique" rows from the table

SELECT FileInfo
FROM sometableyoudidnotname
GROUP BY FileInfo
HAVING COUNT(*) > 1

Now that you know which ones are unique and non unique you can left join to that table to get the "status" and count it up.

SELECT base.FileID, 
       SUM(CASE WHEN u.FileID is NOT NULL THEN 1 ELSE 0 END) as nonunique,
       SUM(CASE WHEN u.FileID is NULL THEN 1 ELSE 0 END) as unique
FROM sometableyoudidnotname base
LEFT JOIN (
  SELECT FileInfo
  FROM sometableyoudidnotname
  GROUP BY FileInfo
  HAVING COUNT(*) > 1
) u ON base.FileInfo = u.FileInfo
GROUP BY base.FileID

回复收藏 0 原文

心舞飞扬 2025-01-17 19:05:47

有一个派生表来计算每个 fileid 的出现次数。 JOIN 和 GROUP BY：

select t1.FileID,
       sum(case when t2.ficount = 1 then 1 else 0 end),
       sum(case when t2.ficount > 1 then 1 else 0 end)
from tablename t1
join
(
    select fileinfo, count(*) ficount
    from tablename
    group by fileinfo
) t2
on t1.fileinfo = t2.fileinfo
group by t1.FileID

Have a derived table that counts occurrences of each fileid. JOIN and GROUP BY:

select t1.FileID,
       sum(case when t2.ficount = 1 then 1 else 0 end),
       sum(case when t2.ficount > 1 then 1 else 0 end)
from tablename t1
join
(
    select fileinfo, count(*) ficount
    from tablename
    group by fileinfo
) t2
on t1.fileinfo = t2.fileinfo
group by t1.FileID

回复收藏 0 原文

~没有更多了~