Блог

У меня есть запрос, который использует ROW_NUMBER() и PARTITION BY и упорядочивает на основе даты col. Что я хотел бы знать, так это то, возможно ли ограничить это и возвращать только col1, которые соответствуют, если второе максимальное значение находится в позиции 1, а текущее максимальное значение находится в позиции 250.

 SELECT t1.col1, t1.col_date, t1.col_val, t1.rno FROM (
  SELECT col1, col_date, col_val, count(*), ROW_NUMBER() OVER (PARTITION BY col1 order by col_date ASC) as rno
FROM table_one
INNER JOIN
  table_two
ON
  table_one.id = table_one_id
WHERE col1 in (
  SELECT
    col1
  FROM
    table_one
  INNER JOIN table_two on table_one.id = table_one_id
  GROUP
    by table_one.id
  HAVING
    COUNT(*) >= 250
  ) 
  GROUP BY col1, col_date, col_val
) t1
WHERE t1.rno < 250
GROUP BY t1.col1, t1.col_date, t1.col_val, t1.rno
ORDER BY t1.col1, t1.col_date;
  

IE-

  col1 |           col_date           | col_val | rno
-------- ---------------------------- -------- -----
 ABC   | 2018-07-18 15:27:35.394051  |   999   |   1
...
 ABC   | 2019-03-24 15:27:34.78493   |   1000  | 250

 XYZ   | 2018-07-18 15:27:35.394051  |   900   |   1
...
 XYZ   | 2019-03-24 15:27:34.78493   |   1001  | 250
  

Будут ли возвращены оба значения, если col_val является вторым максимальным значением в rno 1, а col_val максимальным значением в rno 250.

[ОБНОВИТЬ]

Для наглядности: две таблицы. table_one — это список стандартных символов. table_two — это список исторических цен. Текущий запрос выбирает символы из table_one и объединяет исторические цены из table_two, которые содержат более 250 записей для каждого символа, а отсечение по rno равно 250, поэтому результат будет выглядеть следующим образом.

 symbol |     market_close_date      | close  | rno
-------- ---------------------------- -------- -----
 FAKE   | 2018-07-18 15:27:35.394051 |  250.0 |   1
 FAKE   | 2018-07-19 15:27:35.391866 |  249.0 |   2
 FAKE   | 2018-07-20 15:27:35.389615 |  248.0 |   3
 FAKE   | 2018-07-21 15:27:35.38741  |  247.0 |   4
 FAKE   | 2018-07-22 15:27:35.3852   |  246.0 |   5
 FAKE   | 2018-07-23 15:27:35.383099 |  245.0 |   6
 FAKE   | 2018-07-24 15:27:35.380934 |  244.0 |   7
 FAKE   | 2018-07-25 15:27:35.378828 |  243.0 |   8
 FAKE   | 2018-07-26 15:27:35.376769 |  242.0 |   9
...
 FAKE   | 2019-03-24 15:27:34.78493  | 1000.0 | 250
 TEST   | 2018-07-18 15:27:35.396232 |  250.0 |   1
 ...
 TEST   | 2018-07-18 15:27:35.64352  |  50.0 | 250
  

Если rno 1 > 2 … 249 и rno 1 … 249 < rno 250, это будет справедливо для FAKE, потому что rno 1 является наибольшим приближением к 249, а 250 — наибольшее приближение, ТЕСТ не будет соответствовать критериям.

Я думаю, вы можете использовать здесь ключевые слова FILTER и PARTITION BY . Это не будет решением для копирования и вставки, но я думаю, что это укажет вам правильное направление. Я использовал 10 в качестве ограничения строки, потому что я не хотел создавать поддельные данные, но 250 там можно применить.

Таблица test в этом примере будет результатом вашего первоначального объединения между двумя таблицами. Выбор всех данных (или желаемых полей) и добавление ROW_NUMBER

Идея:

1. Внутренний запрос: выберите нужные данные и примените ROW_NUMBER
2. Средний запрос: примените ограничение строки к начальным результатам (где номер строки <= x ). Выберите максимум всего, что находится под пределом строки x , и максимум всего набора, включая предел строки x
3. Самый внешний запрос: убедитесь, что номер строки 1 равен max (самый высокий close где rno < x ) и убедитесь, что номер строки x равен max в наборе (самый высокий close где rno = x )

 SELECT *
FROM
(
    SELECT  *
            , MAX(close) FILTER(WHERE rno < 10) OVER(PARTITION BY symbol) as "max"
            , MAX(close) OVER(PARTITION BY symbol) AS second_max
    FROM 
    (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY symbol ORDER BY close_date) AS rno
        FROM test
    ) t
    WHERE t.rno <= 10
) t2
WHERE (t2.rno = 1 AND t2.close = t2.max)
   OR (t2.rno = 10 AND t2.close = t2.second_max)
;
  

Вот SQLFiddle, показывающий эту идею.