Столбец Dune SQL с промежуточными итогами и группировкой по

Question

Я пытаюсь написать аналитический запрос Dune, используя их родной язык SQL, используя движок Dune v2. Я не очень опытен в написании SQL-запросов - прошло 10+ лет.

На данный момент я написал следующий запрос, который работает как задумано (можете посмотреть мой публичный дашборд ):

      select
  collection,
  count(collection) as num_transactions
from
  nft.trades
where
  blockchain = "ethereum"
  and collection <> ""
  and block_time >= '{{from_date}}'
group by 1
order by 2 desc

Это возвращает таблицу следующим образом:

      collection                  | num_transactions
----------------------------------------------
GrandpaApes                 | 4666
Ethereum Name Service (ENS) | 4545
OpenSea Collection          | 2319

Как видно из панели инструментов, это корректно возвращает сделки NFT, сгруппированные по коллекциям в порядке убывания количества транзакций.

Что я пытаюсь сделать, так это добавить столбец, содержащий текущую совокупную суммуnum_transactions- назовем этоtot_transactions. Результат, который я хочу, выглядит примерно так:

      collection                  | num_transactions | tot_transactions
-----------------------------------------------------------------
GrandpaApes                 | 4666             | 4666
Ethereum Name Service (ENS) | 4545             | 9211
OpenSea Collection          | 2319             | 11530

Я попытался сделать это, изменив запрос, как показано ниже, но он выдает ошибку; Я прочитал кучу других вопросов SQL на SO, но до сих пор мне не удалось заставить ни один из них работать. Как я должен написать свой запрос, чтобы добиться успеха в Dune?

      select
  collection,
  count(collection) as num_transactions,
  sum(count(collection)) as tot_transactions
from
  nft.trades
where
  blockchain = "{{blockchain}}"
  and collection <> ""
  and block_time >= '{{from_date}}'
group by 1
order by 2 desc

Сообщение об ошибке:

      Error: {42000} [Simba][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.AnalysisException: It is not allowed to use an aggregate function in the argument of another aggregate function. Please use the inner aggregate function in a sub-query.

Примечание. Я вижу, что он говорит мне, что я не могу использовать агрегат внутри другого агрегата (я предполагаю, что это означает, что я не могу использоватьcount()внутриsum()), но я попытался использовать подзапрос, и я не был уверен, как заставить его работать (извинения - у меня больше нет версии запроса, который я написал, в котором есть подзапрос).

Answer 1

Сообщение об ошибке исходит от Apache Hive SQL. Глядя на документацию для Apache Hive SQL, они говорят, что они поддерживают оконные функции, поэтому замените свойsum(count(collection))с

      Sum(count(collection)) Over (Group By collection Order By count(collection) Rows Between Unbounded Preceding and Current Row)

Да, это довольно многословно.

Если это не сработает, оберните этот запрос во второй запрос, где вы используете его в оконной сумме.