A、B、C 列的 SQL：按 A 分组，每个 A 的 B 的计算份额等于 C

发布于 2024-12-09 01:59:42 字数 256 浏览 0 评论 0原文

数据库是Oracle。我有一个包含三列的表，A、B 和 C。

A 是字符串列
B 是 int 列
C 是 int 列

我需要一个 SQL 选择，对 A 进行分组并返回份额（以百分比为单位），其中 B = C = [特定 int 值] 对于 A 的每个值。

类似

SELECT A, percentBEqC
...
GROUP ON A

原文

Database is Oracle. I have a single table with three columns, A, B and C.

A is a string column
B is an int column
C is an int column

I need an SQL select that groups on A and returns the share (in percent) where B = C = [particular int value] for each value of A.

Something like

SELECT A, percentBEqC
...
GROUP ON A

分享到QQ

分享到微博

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

发布评论

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

红ご颜醉 2024-12-16 01:59:42

SELECT A
     , COUNT( CASE WHEN B = C AND C = 256
                   THEN 1
              END 
            )
       / COUNT(*) AS percentBEqC
FROM YourTable
GROUP BY A

您可能需要检查 B = 256 AND C = 256 是否具有更好的性能。

解决你的问题的另一种方法是：

SELECT ta.A
     , COALESCE(cnt,0) / cntAll AS percentBEqC
FROM
    ( SELECT A
           , COUNT(*) AS cntAll
      FROM YourTable
      GROUP BY A
    ) ta
  LEFT JOIN
    ( SELECT A
           , COUNT(*) AS cnt
      FROM YourTable
      WHERE B = 256 AND C = 256
      GROUP BY A
    ) ts
    ON ts.A = ta.A

SELECT A
     , COUNT( CASE WHEN B = C AND C = 256
                   THEN 1
              END 
            )
       / COUNT(*) AS percentBEqC
FROM YourTable
GROUP BY A

You may want to check if B = 256 AND C = 256 has better performance.

Another way for your problem would be:

SELECT ta.A
     , COALESCE(cnt,0) / cntAll AS percentBEqC
FROM
    ( SELECT A
           , COUNT(*) AS cntAll
      FROM YourTable
      GROUP BY A
    ) ta
  LEFT JOIN
    ( SELECT A
           , COUNT(*) AS cnt
      FROM YourTable
      WHERE B = 256 AND C = 256
      GROUP BY A
    ) ts
    ON ts.A = ta.A

回复收藏 0 原文

青春有你 2024-12-16 01:59:42

另一种方式：
_{..一旦语法正确;/}

SELECT A, (COUNT(*) / (SELECT COUNT(*) FROM mytbl b WHERE b.A = a.A)) AS percentBEqC
  FROM mytbl a
 WHERE B = particular_value
   AND C = particular_value
 GROUP BY A;

仅返回百分比BEqC > 的行0,

Another way:
_{..once the syntax is correct ;/}

SELECT A, (COUNT(*) / (SELECT COUNT(*) FROM mytbl b WHERE b.A = a.A)) AS percentBEqC
  FROM mytbl a
 WHERE B = particular_value
   AND C = particular_value
 GROUP BY A;

Returns only rows where percentBEqC > 0,

回复收藏 0 原文

~没有更多了~