【代码报错】DAI文档piovot示例代码出错

由bq2dgth5创建，最终由small_q更新于2025-02-16 03:03 被浏览 26 用户

运行

https://bigquant.com/wiki/doc/dai-PLSbc1SbZX

中的piovt示例代码：

%%sql
/*统计每个指数每天的总成交量*/

CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
    SELECT date, instrument index_code, member_code instrument
    FROM cn_stock_index_component
    WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;

PIVOT bigquant_table ON date USING FIRST(volume);

报错如下：

• \

  BinderException                           Traceback (most recent call last)
  Cell In[6], line 1
  ----> 1 get_ipython().run_cell_magic('sql', '', "/*统计每个指数每天的总成交量*/\n\nCREATE TABLE IF NOT EXISTS bigquant_table AS\nSELECT a.date, a.instrument, b.index_code, a.volume\nFROM cn_stock_bar1d AS a\nINNER JOIN (\n    SELECT date, instrument index_code, member_code instrument\n    FROM cn_stock_index_component\n    WHERE date > '2023-09-11'\n) AS b\nON a.date = b.date AND a.instrument = b.instrument\nWHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')\nORDER BY a.date, a.instrument;\n\nPIVOT_WIDER bigquant_table ON date USING FIRST(volume);\n")
  File /usr/local/python3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:2475, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
     2473 with self.builtin_trap:
     2474     args = (magic_arg_s, cell)
  -> 2475     result = fn(*args, **kwargs)
     2477 # The code below prevents the output from being displayed
     2478 # when using magics with decodator @output_can_be_silenced
     2479 # when the last Python token in the expression is a ';'.
     2480 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):
  File ~/.ipython/profile_default/startup/000-aistudio.py:154, in sql(line, cell, *args, **kwargs)
      152     return dai.query(line, full_db_scan=True)
      153 elif not line:
  --> 154     return dai.query(cell, full_db_scan=True)
      155 else:
      156     filters = json.loads(line.replace("'", '"'))
  File /var/app/enabled/dai/_functions.py:143, in query(sql, udf_list, full_db_scan, filters)
  BinderException: Binder Error: Referenced column "__internal_pivot_aggregate1" not found in FROM clause!
  Candidate bindings: "unnamed_subquery8.__internal_pivot_ref1"
  

\