时间戳

首页 > 解决方案 > group by 子句和聚合函数的问题

sql - group by 子句和聚合函数的问题

问题描述

我正在处理这个问题：对于每个证券交易所，显示总交易量最高的股票代码。显示证券交易所名称、股票代码和总交易量。按证券交易所名称和股票代码对输出进行排序。

这是ERD

这是我的查询和输出：

SELECT 
    se.name AS "stock exchange name",
    sl.stock_symbol AS "stock symbol",
    SUM(t.shares) AS "highest total trade volume"
FROM stock_exchange se
    JOIN trade t
        ON se.stock_ex_id = t.stock_ex_id
    JOIN company c
        ON t.stock_id = c.stock_id
    JOIN stock_listing sl
        ON se.stock_ex_id = sl.stock_ex_id and sl.stock_id = c.stock_id
GROUP BY sl.stock_symbol,se.name
HAVING SUM(t.shares) =(
    SELECT 
        MAX(SUM(t.shares))
    FROM(
        SELECT 
            SUM(shares) over(partition by sx.stock_ex_id)
        FROM stock_exchange sx
            JOIN trade tr
               ON tr.stock_ex_id = sx.stock_ex_id
            JOIN company co
                ON co.stock_id = tr.stock_id
    )
)
ORDER BY se.name, sl.stock_symbol;



   Stock exchange name                 stock symbol      highest total trade volumn
   1 Euronext Paris                       BNP              5000
   2 Euronext Paris                       EDF:EN PARIS     78600
   3 London Stock Exchange                BA               126400
   4 NASDAQ Stock Exchange                TESO             164100
   5 New York Stock Exchange              GOOG             132300
   6 New York Stock Exchange              NYT              111900
   7 New York Stock Exchange              TM               50300
   8 Tokyo Stock Exchange                 TYO:6201         5300

第 1,6 和 7 行不是该证券交易所的最高交易量，并且不知何故仍显示在输出中。我注意到当我不按函数将 sl.stock_symbol 分组时，它会打印预期的确切行。但是我需要显示这个股票代码列，那么我该如何解决呢？

标签： sqloracle

解决方案

好吧，如果没有数据样本，很难知道你真正想要什么。但是，正如您所说，删除股票代码会为您提供正确的值，也许从 group by 中删除该部分并在 group by 之后的新选择中恢复该字段是一个好主意。您没有使用该字段进行计算。

我不知道你正在处理的卷，所以性能方面不知道它会如何表现

例子

select r.se_name  AS "stock exchange name",
       t.stock_symbol AS "stock symbol",
       r.max_shares as "highest total trade volumn"
from 
(
SELECT 
  se.name,
  se.stock_ex_id
  -- sl.stock_symbol ,
  SUM(t.shares) as max_shares 
FROM stock_exchange se
    JOIN trade t
        ON se.stock_ex_id = t.stock_ex_id
    JOIN company c
        ON t.stock_id = c.stock_id
    JOIN stock_listing sl
        ON se.stock_ex_id = sl.stock_ex_id and sl.stock_id = c.stock_id
GROUP BY sl.stock_symbol
HAVING SUM(t.shares) =(
    SELECT 
        MAX(SUM(t.shares))
    FROM(
        SELECT 
            SUM(shares) over(partition by sx.stock_ex_id)
        FROM stock_exchange sx
            JOIN trade tr
               ON tr.stock_ex_id = sx.stock_ex_id
            JOIN company co
                ON co.stock_id = tr.stock_id
    )
) 
) r join stock_listing t on r.stock_ex_id = t.stock_ex_id 
ORDER BY r.se_name, t.stock_symbol;

推荐阅读