WITH latest_profile AS (
|
SELECT MAX(snapshot_date) AS snapshot_date
|
FROM a_share_profile_snapshot
|
WHERE snapshot_date <= '2026-07-04'
|
),
|
universe AS (
|
SELECT
|
p.symbol,
|
p.stock_name,
|
p.industry_l1,
|
p.industry_l2,
|
p.concepts_raw,
|
p.main_business_raw
|
FROM a_share_profile_snapshot p
|
JOIN latest_profile lp ON lp.snapshot_date = p.snapshot_date
|
WHERE p.status = 'OK'
|
AND ((p.industry_l1 LIKE '%机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%机器人%' ESCAPE '\\' OR p.stock_name LIKE '%机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%人形机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%人形机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%人形机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%人形机器人%' ESCAPE '\\' OR p.stock_name LIKE '%人形机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%工业机器人%' ESCAPE '\\' OR p.industry_l2 LIKE '%工业机器人%' ESCAPE '\\' OR p.concepts_raw LIKE '%工业机器人%' ESCAPE '\\' OR p.main_business_raw LIKE '%工业机器人%' ESCAPE '\\' OR p.stock_name LIKE '%工业机器人%' ESCAPE '\\') OR (p.industry_l1 LIKE '%机器视觉%' ESCAPE '\\' OR p.industry_l2 LIKE '%机器视觉%' ESCAPE '\\' OR p.concepts_raw LIKE '%机器视觉%' ESCAPE '\\' OR p.main_business_raw LIKE '%机器视觉%' ESCAPE '\\' OR p.stock_name LIKE '%机器视觉%' ESCAPE '\\') OR (p.industry_l1 LIKE '%减速器%' ESCAPE '\\' OR p.industry_l2 LIKE '%减速器%' ESCAPE '\\' OR p.concepts_raw LIKE '%减速器%' ESCAPE '\\' OR p.main_business_raw LIKE '%减速器%' ESCAPE '\\' OR p.stock_name LIKE '%减速器%' ESCAPE '\\') OR (p.industry_l1 LIKE '%伺服%' ESCAPE '\\' OR p.industry_l2 LIKE '%伺服%' ESCAPE '\\' OR p.concepts_raw LIKE '%伺服%' ESCAPE '\\' OR p.main_business_raw LIKE '%伺服%' ESCAPE '\\' OR p.stock_name LIKE '%伺服%' ESCAPE '\\') OR (p.industry_l1 LIKE '%丝杠%' ESCAPE '\\' OR p.industry_l2 LIKE '%丝杠%' ESCAPE '\\' OR p.concepts_raw LIKE '%丝杠%' ESCAPE '\\' OR p.main_business_raw LIKE '%丝杠%' ESCAPE '\\' OR p.stock_name LIKE '%丝杠%' ESCAPE '\\') OR (p.industry_l1 LIKE '%灵巧手%' ESCAPE '\\' OR p.industry_l2 LIKE '%灵巧手%' ESCAPE '\\' OR p.concepts_raw LIKE '%灵巧手%' ESCAPE '\\' OR p.main_business_raw LIKE '%灵巧手%' ESCAPE '\\' OR p.stock_name LIKE '%灵巧手%' ESCAPE '\\') OR (p.industry_l1 LIKE '%传感器%' ESCAPE '\\' OR p.industry_l2 LIKE '%传感器%' ESCAPE '\\' OR p.concepts_raw LIKE '%传感器%' ESCAPE '\\' OR p.main_business_raw LIKE '%传感器%' ESCAPE '\\' OR p.stock_name LIKE '%传感器%' ESCAPE '\\') OR (p.industry_l1 LIKE '%具身智能%' ESCAPE '\\' OR p.industry_l2 LIKE '%具身智能%' ESCAPE '\\' OR p.concepts_raw LIKE '%具身智能%' ESCAPE '\\' OR p.main_business_raw LIKE '%具身智能%' ESCAPE '\\' OR p.stock_name LIKE '%具身智能%' ESCAPE '\\'))
|
),
|
price_with_prev AS (
|
SELECT
|
d.symbol,
|
d.trade_date,
|
d.open_price,
|
d.high_price,
|
d.low_price,
|
d.close_price,
|
d.volume,
|
d.amount,
|
LAG(d.close_price) OVER (PARTITION BY d.symbol ORDER BY d.trade_date) AS prev_close,
|
AVG(d.amount) OVER (
|
PARTITION BY d.symbol
|
ORDER BY d.trade_date
|
ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING
|
) AS avg_amount_20
|
FROM a_share_daily_price d
|
JOIN universe u ON u.symbol = d.symbol
|
WHERE d.trade_date BETWEEN DATE_SUB('2026-04-04', INTERVAL 30 DAY) AND '2026-07-04'
|
),
|
window_rows AS (
|
SELECT
|
u.stock_name,
|
u.industry_l1,
|
u.industry_l2,
|
u.concepts_raw,
|
u.main_business_raw,
|
p.*,
|
CASE
|
WHEN p.prev_close IS NULL OR p.prev_close = 0 THEN NULL
|
ELSE (p.close_price / p.prev_close - 1) * 100
|
END AS daily_return_pct,
|
CASE
|
WHEN p.avg_amount_20 IS NULL OR p.avg_amount_20 = 0 THEN NULL
|
ELSE p.amount / p.avg_amount_20
|
END AS amount_ratio_20
|
FROM price_with_prev p
|
JOIN universe u ON u.symbol = p.symbol
|
WHERE p.trade_date BETWEEN '2026-04-04' AND '2026-07-04'
|
),
|
ranked AS (
|
SELECT
|
w.*,
|
ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date) AS rn_first,
|
ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date DESC) AS rn_last
|
FROM window_rows w
|
),
|
agg AS (
|
SELECT
|
symbol,
|
MAX(stock_name) AS company_name,
|
MAX(industry_l1) AS industry_l1,
|
MAX(industry_l2) AS industry_l2,
|
MAX(concepts_raw) AS concepts_raw,
|
MAX(main_business_raw) AS main_business_raw,
|
MIN(trade_date) AS window_start,
|
MAX(trade_date) AS window_end,
|
MAX(CASE WHEN rn_first = 1 THEN close_price END) AS first_close,
|
MAX(CASE WHEN rn_last = 1 THEN close_price END) AS last_close,
|
COUNT(*) AS trading_days,
|
SUM(CASE WHEN daily_return_pct >= 9.5 THEN 1 ELSE 0 END) AS strong_up_days,
|
MAX(daily_return_pct) AS max_daily_return_pct,
|
MAX(amount_ratio_20) AS max_amount_ratio_20,
|
MAX(amount) AS max_amount
|
FROM ranked
|
GROUP BY symbol
|
),
|
scored AS (
|
SELECT
|
*,
|
CASE
|
WHEN first_close IS NULL OR first_close = 0 THEN NULL
|
ELSE (last_close / first_close - 1) * 100
|
END AS period_return_pct
|
FROM agg
|
)
|
SELECT
|
'SCAN-ANA-ROBOT-MARKET-GAP-20260704-001' AS scan_id,
|
'机器人案例' AS industry_case,
|
symbol,
|
company_name,
|
industry_l1,
|
industry_l2,
|
window_start,
|
window_end,
|
trading_days,
|
ROUND(first_close, 4) AS first_close,
|
ROUND(last_close, 4) AS last_close,
|
ROUND(period_return_pct, 4) AS period_return_pct,
|
strong_up_days,
|
ROUND(max_daily_return_pct, 4) AS max_daily_return_pct,
|
ROUND(max_amount_ratio_20, 4) AS max_amount_ratio_20,
|
CASE
|
WHEN period_return_pct >= 20.0 OR strong_up_days >= 1 OR max_amount_ratio_20 >= 2.0
|
THEN 'STRONG_MANIFESTATION'
|
ELSE 'WEAK_OR_NORMAL'
|
END AS manifestation_type,
|
'REVIEW_REQUIRED' AS scope_type,
|
'REVIEW_REQUIRED' AS suspected_missing_track,
|
'MARKET_REVERSE_GAP_SCAN' AS gap_type,
|
CASE
|
WHEN period_return_pct >= 20.0 OR strong_up_days >= 1 OR max_amount_ratio_20 >= 2.0
|
THEN 1
|
ELSE 0
|
END AS supplement_required_flag,
|
'Review core business against industry scheme before adding to evidence queue' AS supplement_question,
|
'DRAFT_FOR_REVIEW' AS review_status,
|
LEFT(concepts_raw, 500) AS concepts_excerpt,
|
LEFT(main_business_raw, 500) AS main_business_excerpt
|
FROM scored
|
WHERE period_return_pct >= 20.0
|
OR strong_up_days >= 1
|
OR max_amount_ratio_20 >= 2.0
|
ORDER BY supplement_required_flag DESC, period_return_pct DESC, strong_up_days DESC, max_amount_ratio_20 DESC;
|
