SELECT *, to_char(time, 'Day') as weekday,
CASE
WHEN close > open THEN 'Bull'
ELSE 'Bear'
END AS bull_bear
FROM dukasohlc_day where symbol = 'US30' order by time asc limit 300;
WITH t AS (
SELECT *, to_char(time, 'Day') as weekday
, EXTRACT(isodow from time) as dy
, EXTRACT(week from time) as wk
, LAG(extract(week from time)) OVER (ORDER BY time) as prev_wk
, CASE WHEN close > open THEN 'Bull'
ELSE 'Bear'
END AS bull_bear
FROM dukasohlc_day where symbol = 'US30'
)
SELECT *
FROM t AS t1
WHERE t1.wk = 2
AND EXTRACT(year FROM time) = 2022
AND t1.dy = 1
AND EXISTS (
-- was last friday bear?
SELECT 1 FROM t AS t2
WHERE t2.prev_wk = t1.wk - 1
AND t2.dy = 5
AND t2.bull_bear = 'Bear'
AND EXTRACT(year FROM t2.time) = EXTRACT(year FROM t1.time)
)
AND EXISTS (
-- was last thursday bear?
SELECT 1 FROM t AS t2
WHERE t2.prev_wk = t1.wk - 1
AND t2.dy = 4
AND t2.bull_bear = 'Bear'
AND EXTRACT(year FROM t2.time) = EXTRACT(year FROM t1.time)
)
ORDER BY time asc limit 300;
如果我们用 JOIN 替换 EXISTS,就可以检查日期和 bull_bear 值:
WITH t AS (
SELECT *, to_char(time, 'Day') as weekday
, EXTRACT(isodow from time) as dy
, EXTRACT(week from time) as wk
, LAG(extract(week from time)) OVER (ORDER BY time) as prev_wk
, CASE WHEN close > open THEN 'Bull'
ELSE 'Bear'
END AS bull_bear
FROM dukasohlc_day where symbol = 'US30'
)
SELECT t1.*, t2.weekday, t2.bull_bear, t3.weekday, t3.bull_bear
FROM t AS t1
JOIN t AS t2
ON t2.prev_wk = t1.wk - 1
AND t2.dy = 5
AND t2.bull_bear = 'Bear'
AND EXTRACT(year FROM t2.time) = EXTRACT(year FROM t1.time)
JOIN t AS t3
ON t3.prev_wk = t1.wk - 1
AND t3.dy = 4
AND t3.bull_bear = 'Bear'
AND EXTRACT(year FROM t3.time) = EXTRACT(year FROM t1.time)
WHERE t1.dy = 1
ORDER BY time asc limit 300;
这是一个示例,您需要对其进行更改,以便它仅考虑同一年的前一周(如果周为 1,则为前一年),但无论如何我都会发布它:
如果我们用 JOIN 替换 EXISTS,就可以检查日期和 bull_bear 值:
请注意,除非由市场处理(新年期间关闭),否则这不会处理年初/年底