PostgreSQL:如何截断和分组时间戳？

Question

我正在使用一个数据库，并使用以下查询：

SELECT
  evt_block_time,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      evt_block_time
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      evt_block_time
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

以下是它回报的一瞥：

我想做几件事。首先，将时间戳( evt_block_time )按周截断，然后逐周分组。

• 注意:我尝试在每个select语句下使用date_trunc('week'，evt_block_time)，但是它会引发一个错误。见下文：

SELECT
  date_trunc('week', evt_block_time),
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) OVER (
    ORDER BY
      evt_block_time
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time)
    FROM
      uniswap."Factory_evt_NewExchange"
    ORDER BY
      evt_block_time
  ) as creations

返回：

第31行第26位置不存在"evt_block_time“栏。

此外，虽然我想这不是必需的，但我只想查询过去52周(1年)的数据。

显然，我对SQL有点陌生，但我正在尽我最大的努力。任何帮助都将不胜感激！

Answer 1

问题是从子查询中选择evt_block_time，但是子查询不再包含evt_block_time，而是包含date_trunc('week', evt_block_time)。

要解决这个问题，请给它一个类似于evt_block_week的名称并选择它。

因为它是一个计算的列，所以不能按它进行排序，但是子查询中的order没有任何作用。把它移开。如果要应用订单，请在周围的查询中执行。

计数过滤器中的订单也不起任何作用，订单对于计数来说并不重要。把他们移走。

最后，为了获得每周时间戳的每个版本的数量，由evt_block_week分组。也是由evt_block_week订购的。

SELECT
  evt_block_week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
FROM
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations
group by evt_block_week
order by evt_block_week

如果您只想做一个星期的范围，使用generate_series生成一个周的列表。如果您想要查看所有的周，使用它作为from子查询和left join与creations。按生成的周排序和分组。

SELECT
  weeks.week,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v1'
  ) as v1_pairs,
  COUNT(*) filter (
    WHERE
      uniswap_version = 'v2'
  ) as v2_pairs
from (
  select
    generate_series(
      date_trunc('week', '2020-01-01'::date), date_trunc('week', '2020-12-31'::date), '1 week'
    ) as week
) as weeks 
left join
  (
    SELECT
      'v2' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT
      'v1' as uniswap_version,
      date_trunc('week', evt_block_time) as evt_block_week
    FROM
      uniswap."Factory_evt_NewExchange"
  ) as creations on weeks.week = evt_block_week
group by week
order by week

游行示威。

Answer 2

您的子查询creations没有别名evt_block_time的列，因此不能在主查询中使用该列名。