sql - 使用 PostgreSQL 的 CTE 按时间戳过滤不起作用

Question

我每个都有多个价格locl_ntwrk_id。

我想要完成的是只选择一个价格gateway.pricings.charges.t_valid <= CURRENT_TIMESTAMP(6)。

但是我目前无法得到任何东西。

---

表 locales.countries：

+--------------+--------------+
|locl_ctry_id  |icc           |
+--------------+--------------+    
|218           |387           |
|219           |385           |
|220           |381           |
|297           |382           |
+--------------+--------------+

表 locales.networks：

+--------------+--------------+
|locl_ntwrk_id |locl_ctry_id  |
+--------------+--------------+
|21890         |218           |
|21805         |218           |
|21803         |218           |
|21910         |219           |
|21902         |219           |
|21901         |219           |
|21912         |219           |
+--------------+--------------+

和表定价。收费：

+--------------+---------------------------+--------------+--------------+
|locl_ntwrk_id |t_valid                    |locl_ccy_id   |cost          |
+--------------+---------------------------+--------------+--------------+
|21890         |2017-02-06 00:00:00.000000 |978           |1.0           |
|21890         |2019-04-01 00:00:00.000000 |978           |2.0           |
|21805         |2019-04-01 00:00:00.000000 |978           |2.0           |
|21805         |2017-02-06 00:00:00.000000 |978           |1.0           |
|21803         |2017-02-06 00:00:00.000000 |978           |1.0           |
+--------------+---------------------------+--------------+--------------+

我的 SQL：

WITH cte_pricings AS (
   SELECT 
      gateway.pricings.charges.locl_ntwrk_id, 
      gateway.pricings.charges.t_valid, 
      gateway.pricings.charges.locl_ccy_id, 
      gateway.pricings.charges.cost

   FROM gateway.pricings.charges

   WHERE gateway.pricings.charges.t_valid <= CURRENT_TIMESTAMP(6) 
)
   SELECT
      gateway.locales.countries.locl_ctry_id, 
      gateway.locales.countries.icc,
      gateway.locales.networks.locl_ntwrk_id,
      gateway.locales.networks.locl_ctry_id

   FROM gateway.locales.countries

   LEFT JOIN gateway.locales.networks USING (locl_ctry_id)

   LEFT JOIN cte_pricings USING (locl_ntwrk_id);

   WHERE left(gateway.locales.countries.icc::text, 2) IN ('38')

如果CURRENT_TIMESTAMP是 2019-04-02 的预期结果：

+--------------+--------------+--------------+---------------------------+--------------+--------------+
|locl_ctry_id  |icc           |locl_ntwrk_id |t_valid                    |locl_ccy_id   |cost          |
+--------------+--------------+--------------+---------------------------+--------------+--------------+
|218           |387           |21890         |2019-04-01 00:00:00.000000 |978           |2.0           |
|218           |387           |21805         |2019-04-01 00:00:00.000000 |978           |2.0           |
|218           |387           |21803         |2017-02-06 00:00:00.000000 |978           |1.0           |
+--------------+--------------+--------------+---------------------------+--------------+--------------+

如果CURRENT_TIMESTAMP是 2019-03-20 的预期结果：

+--------------+--------------+--------------+---------------------------+--------------+--------------+
|locl_ctry_id  |icc           |locl_ntwrk_id |t_valid                    |locl_ccy_id   |cost          |
+--------------+--------------+--------------+---------------------------+--------------+--------------+
|218           |387           |21890         |2017-02-06 00:00:00.000000 |978           |1.0           |
|218           |387           |21805         |2017-02-06 00:00:00.000000 |978           |1.0           |
|218           |387           |21803         |2017-02-06 00:00:00.000000 |978           |1.0           |
+--------------+--------------+--------------+---------------------------+--------------+--------------+

标签： sqlpostgresql

Answer 1

如果我理解正确，您可以使用distinct on：

SELECT DISTINCT ON (locl_ctry_id, icc, locl_ntwrk_id) . . .
FROM gateway.locales.countries c JOIN
     gateway.locales.networks
     USING (locl_ctry_id) JOIN
     gateway.pricings.charges ch
     USING (locl_ntwrk_id)
WHERE ch.t_valid <= CURRENT_TIMESTAMP(6)
ORDER BY locl_ctry_id, icc, locl_ntwrk_id, ch.t_valid DESC