合并三个数据表的方法
由bq13kdee创建，最终由bq13kdee 被浏览 2 用户
from bigmodule import M
import dai
# 假设 M 已在环境中可用
m1 = M.input_features_dai.v30(
mode="SQL",
sql="""
SELECT
a.date,
a.instrument,
a.* EXCLUDE(date, instrument),
b.pe_ttm AS pe_ttm,
b.total_market_cap AS market_cap,
a.close / m_lag(a.close, 1) AS return_1,
(a.close - a.open) / a.open AS intraday_return,
m.netflow_amount_main as net,
m.inflow_amount_rate_al as inflow
FROM cn_stock_bar1d a
LEFT JOIN cn_stock_prefactors b USING (date, instrument)
LEFT JOIN cn_stock_moneyflow m USING (date, instrument)
-- WHERE a.date BETWEEN '2023-01-01' AND '2023-01-31' AND a.st_status = 0
-- 去掉有空值的行
QUALIFY
-- QUALIFY 过滤, 在窗口等计算算子之后执行, 比如 m_lag(close, 3) AS close_3, 对于 close_3 的过滤需要放到这里
-- 去掉有空值的行
COLUMNS(*) IS NOT NULL
ORDER BY date, instrument
""",
extra_fields="date, instrument",
m_name="m1"
)
m2 = M.extract_data_dai.v20(
sql=m1.data,
start_date="""2023-01-01""",
start_date_bound_to_trading_date=False,
end_date="""2024-12-31""",
end_date_bound_to_trading_date=False,
before_start_days=90,
keep_before=False,
debug=True,
m_name="""m2"""
)
#但是提示错误
"""
[2025-12-02 17:44:50] INFO: input_features_dai.v30 开始运行 ..[2025-12-02 17:44:50] INFO: sql mode[2025-12-02 17:44:50] INFO: input_features_dai.v30 运行完成 [0.035s].[2025-12-02 17:44:50] INFO: extract_data_dai.v20 开始运行 ..[2025-12-02 17:44:50] [debug ]
SELECT
a.date,
a.instrument,
a.* EXCLUDE(date, instrument),
b.pe_ttm AS pe_ttm,
b.total_market_cap AS market_cap,
a.close / m_lag(a.close, 1) AS return_1,
(a.close - a.open) / a.open AS intraday_return,
m.netflow_amount_main as net,
m.inflow_amount_rate_al as inflow
FROM cn_stock_bar1d a
LEFT JOIN cn_stock_prefactors b USING (date, instrument)
LEFT JOIN cn_stock_moneyflow m USING (date, instrument)
-- WHERE a.date BETWEEN '2023-01-01' AND '2023-01-31' AND a.st_status = 0
-- 去掉有空值的行
QUALIFY
-- QUALIFY 过滤, 在窗口等计算算子之后执行, 比如 m_lag(close, 3) AS close_3, 对于 close_3 的过滤需要放到这里
-- 去掉有空值的行
COLUMNS(*) IS NOT NULL
ORDER BY date, instrument
[2025-12-02 17:44:50] WARNING: start_date='2023-01-01', end_date='2024-12-31', query_start_date='2022-10-03 00:00:00' (支持加速 升级资源"") ..
"""
"""
您可以去社区论坛问答交流板块反馈咨询 去发帖>>
---------------------------------------------------------------------------
InvalidInputException Traceback (most recent call last)
Cell In[24], line 31
3 # 假设 M 已在环境中可用
4 m1 = M.input_features_dai.v30(
5 mode="SQL",
6 sql="""
(...)
29 m_name="m1"
30 )
---> 31 m2 = M.extract_data_dai.v20(
32 sql=m1.data,
33 start_date="""2023-01-01""",
34 start_date_bound_to_trading_date=False,
35 end_date="""2024-12-31""",
36 end_date_bound_to_trading_date=False,
37 before_start_days=90,
38 keep_before=False,
39 debug=True,
40 m_name="""m2"""
41 )
File /opt/pyenv/versions/3.11.8/lib/python3.11/site-packages/bigmodule/modules.py:28, in __call__(self, **kwargs)
File /opt/pyenv/versions/3.11.8/lib/python3.11/site-packages/bigmodule/moduleinvoker.py:218, in module_invoke(name, version, kwargs)
File /opt/pyenv/versions/3.11.8/lib/python3.11/site-packages/bigmodule/moduleinvoker.py:181, in _module_invoke(name, version, kwargs)
File /opt/pyenv/versions/3.11.8/lib/python3.11/site-packages/bigmodule/moduleinvoker.py:44, in _module_run(module, kwargs)
File dist/build/extract_data_dai/v20/__init__.py:78, in v20.run()
File /var/app/enabled/dai/_telemetry.py:128, in wrapper(*args, **kwargs)
File /var/app/enabled/dai/_functions.py:152, in df(self)
InvalidInputException: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result
Error: Binder Error: Referenced column m.inflow_amount_rate_al not found in FROM clause and can't find in alias map.
LINE 1: ..._amount_rate_al AS inflow FROM cn_stock_bar1d AS a LEFT JOIN cn_stock_prefactors AS b USING (date, instrument) LEFT...
"""
\