В прошлом посте мы начали копаться в истории моего кошелька с течением времени. Мы выбрали один момент времени для оценки.
Обычно при просмотре исторических данных важнее увидеть, как они менялись с течением времени.
Например, вместо того, чтобы просто подсчитывать общее количество потраченного газа, мы на самом деле хотим показать, как это значение увеличивалось с течением времени.
Было ли это постепенным или быстрым ростом?
В Postgres есть очень простая функция для этого, которая называется SUM([Столбец значений]) OVER (ORDER BY [Столбец времени]), при этом [Столбец времени] также включен в оператор SELECT.
Давайте вернемся к примеру с таблицей транзакций о потраченном газе и протестируем эту функцию OVER;
Выберите
sum(((“gas_price”*”gas_used”)/10¹⁸) * price.”price”)
OVER (ORDER BY date_trunc('day',txs.”block_time”) ASC) AS «gas_usd_cost»,
date_trunc('day',txs.»block_time») AS «tx_day»
из ethereum.»transactions» txs< br /> левое соединение (
ВЫБЕРИТЕ «минуту», «цену»
из цен «USD»
Где символ = «WETH») как «цены» НА цены. «минута» = date_trunc('минута',txs.”block_time”)
где txs.”from” = '\xb9479331DF573d6BCa8b924f48e8CACB84664cE7'
Я добавил функцию OVER и день транзакции. Обратите внимание: я не использую предложение GROUP BY в этой функции. Это было то, что я изначально делал по привычке, но эта функция работает не так.
Поскольку я не использую GROUP BY, вы увидите, что я получаю некоторые непреднамеренные результаты, когда один день появляется несколько раз. Самый первый день 2019-09-04 указан 3X.
Это связано с тем, что в моей таблице транзакций есть 3 строки для 2019–09–14, но, поскольку я не группирую по дням транзакций, в ней перечислено все.
Чтобы исправить это, мне просто нужно немного изменить порядок моего запроса. Мне нужно сначала СУММИТЬ и СГРУППИРОВАТЬ ПО дню, а затем я могу использовать свою функцию НАВЕРХ для этого результата.
С КАК «Стоимость_газа» (
Выберите
сумма(((«цена_газа»*»использованный_газ»)/10¹⁸) * цены.»цена») КАК «стоимость_газа_доллары»,
date_trunc ('day',txs.”block_time”) AS “tx_day”
from ethereum. “transactions” txs
left join (
SELECT “minute”, “price”
from цены."usd"
Где символ = 'WETH') как "цены" НА цены. "минута" = date_trunc('минута', txs. "время_блока")
где txs. "from" = '\xb9479331DF573d6BCa8b924f48e8CACB84664cE7'
ГРУППИРОВАТЬ ПО date_trunc('day',txs.”block_time”)
ВЫБРАТЬ
sum(“gas_usd_cost”)
OVER ( ORDER BY «tx_day» ASC) AS «gas_usd_cost_ttl»,
«tx_day»
ОТ «Gas_Cost»
В первый день транзакции GROUP BY я использовал CTE (выражение общей таблицы), которое я назвал Gas_used, а затем выбрал из этого CTE, используя функцию OVER, чтобы суммировать значение с течением времени.
Работает как шарм.
Расчет с течением времени можно использовать для других функций агрегирования, таких как AVG или, возможно, даже для более популярного RANK.
RANK действительно забавна, потому что позволяет вам вернуть номер строки для упорядоченного результата. Например, вы можете вернуть 5-ю самую дорогую транзакцию, которую вы когда-либо совершали. Немного неубедительный пример, но позже мы найдем лучший способ его использования.
В следующем посте я перейду от SQL к некоторым визуализациям. В Dune есть несколько очень хороших опций для визуальных эффектов, и их очень легко реализовать, как только вы заработаете с SQL.
