setup_duckdb.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## cn_stock/bars/daily"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌─────────────────────┬──────────┬───────┬───────┬───────┬───┬───────────────┬─────────┬─────────┬─────────┬─────────┐\n",
      "│         dt          │   name   │ _open │ _high │ _low  │ … │ lb_down_count │  close  │  open   │  high   │   low   │\n",
      "│     timestamp_s     │ varchar  │ float │ float │ float │   │     uint8     │  float  │  float  │  float  │  float  │\n",
      "├─────────────────────┼──────────┼───────┼───────┼───────┼───┼───────────────┼─────────┼─────────┼─────────┼─────────┤\n",
      "│ 1991-01-03 00:00:00 │ 深发展Ａ │  66.4 │  66.4 │  66.4 │ … │             0 │ 7.07077 │ 7.07077 │ 7.07077 │ 7.07077 │\n",
      "│ 1991-01-04 00:00:00 │ 深发展Ａ │ 66.07 │ 66.07 │ 66.07 │ … │             0 │ 7.03562 │ 7.03562 │ 7.03562 │ 7.03562 │\n",
      "│ 1991-01-07 00:00:00 │ 深发展Ａ │ 66.42 │ 66.42 │ 66.42 │ … │             0 │  7.0729 │  7.0729 │  7.0729 │  7.0729 │\n",
      "│ 1991-01-09 00:00:00 │ 深发展Ａ │ 65.76 │ 65.76 │ 65.76 │ … │             0 │ 7.00261 │ 7.00261 │ 7.00261 │ 7.00261 │\n",
      "│ 1991-01-10 00:00:00 │ 深发展Ａ │ 65.43 │ 65.43 │ 65.43 │ … │             0 │ 6.96747 │ 6.96747 │ 6.96747 │ 6.96747 │\n",
      "│ 1991-01-11 00:00:00 │ 深发展Ａ │  65.1 │  65.1 │  65.1 │ … │             0 │ 6.93233 │ 6.93233 │ 6.93233 │ 6.93233 │\n",
      "│ 1991-01-14 00:00:00 │ 深发展Ａ │ 64.45 │ 64.45 │ 64.45 │ … │             0 │ 6.86311 │ 6.86311 │ 6.86311 │ 6.86311 │\n",
      "│ 1991-01-15 00:00:00 │ 深发展Ａ │ 64.13 │ 64.13 │ 64.13 │ … │             0 │ 6.82904 │ 6.82904 │ 6.82904 │ 6.82904 │\n",
      "│ 1991-01-16 00:00:00 │ 深发展Ａ │ 63.81 │ 63.81 │ 63.81 │ … │             0 │ 6.79496 │ 6.79496 │ 6.79496 │ 6.79496 │\n",
      "│ 1991-01-17 00:00:00 │ 深发展Ａ │ 63.49 │ 63.49 │ 63.49 │ … │             0 │ 6.76089 │ 6.76089 │ 6.76089 │ 6.76089 │\n",
      "│          ·          │    ·     │   ·   │    ·  │   ·   │ · │             · │     ·   │     ·   │      ·  │     ·   │\n",
      "│          ·          │    ·     │   ·   │    ·  │   ·   │ · │             · │     ·   │     ·   │      ·  │     ·   │\n",
      "│          ·          │    ·     │   ·   │    ·  │   ·   │ · │             · │     ·   │     ·   │      ·  │     ·   │\n",
      "│ 2024-08-19 00:00:00 │ 平安银行 │ 10.13 │  10.3 │ 10.12 │ … │             0 │   10.29 │   10.13 │    10.3 │   10.12 │\n",
      "│ 2024-08-20 00:00:00 │ 平安银行 │ 10.32 │ 10.43 │ 10.28 │ … │             0 │   10.34 │   10.32 │   10.43 │   10.28 │\n",
      "│ 2024-08-21 00:00:00 │ 平安银行 │  10.3 │ 10.38 │ 10.21 │ … │             0 │   10.35 │    10.3 │   10.38 │   10.21 │\n",
      "│ 2024-08-22 00:00:00 │ 平安银行 │ 10.34 │ 10.45 │ 10.32 │ … │             0 │   10.38 │   10.34 │   10.45 │   10.32 │\n",
      "│ 2024-08-23 00:00:00 │ 平安银行 │ 10.39 │  10.5 │ 10.32 │ … │             0 │   10.47 │   10.39 │    10.5 │   10.32 │\n",
      "│ 2024-08-26 00:00:00 │ 平安银行 │  10.5 │ 10.55 │ 10.42 │ … │             0 │   10.49 │    10.5 │   10.55 │   10.42 │\n",
      "│ 2024-08-27 00:00:00 │ 平安银行 │ 10.47 │ 10.52 │ 10.37 │ … │             0 │   10.43 │   10.47 │   10.52 │   10.37 │\n",
      "│ 2024-08-28 00:00:00 │ 平安银行 │ 10.41 │ 10.43 │ 10.28 │ … │             0 │   10.33 │   10.41 │   10.43 │   10.28 │\n",
      "│ 2024-08-29 00:00:00 │ 平安银行 │ 10.31 │ 10.38 │ 10.11 │ … │             0 │   10.13 │   10.31 │   10.38 │   10.11 │\n",
      "│ 2024-08-30 00:00:00 │ 平安银行 │ 10.11 │ 10.26 │ 10.11 │ … │             0 │   10.16 │   10.11 │   10.26 │   10.11 │\n",
      "├─────────────────────┴──────────┴───────┴───────┴───────┴───┴───────────────┴─────────┴─────────┴─────────┴─────────┤\n",
      "│ 7990 rows (20 shown)                                                                         48 columns (10 shown) │\n",
      "└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import duckdb\n",
    "\n",
    "with duckdb.connect(\"datas/duckdb/test_finance.db\") as conn:\n",
    "    with open(\"finance.duckdb.sql\") as f:\n",
    "        # conn.execute(\"drop table bars_daily_000001_SZ;\")\n",
    "        sql = f.read().format(tablename='000001_SZ', csv_path='datasources/000001.SZ')\n",
    "        conn.execute(sql)\n",
    "    conn.table('bars_daily_000001_SZ').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────────────────┬────────────┬────────┬──────────┬───┬────────────┬────────────┬─────────────┬─────────────┐\n",
      "│          dt          │ last_price │ volume │  amount  │ … │ bid_price1 │ ask_price1 │ bid_volume1 │ ask_volume1 │\n",
      "│      timestamp       │   float    │ uint32 │  uint32  │   │   float    │   float    │   uint32    │   uint32    │\n",
      "├──────────────────────┼────────────┼────────┼──────────┼───┼────────────┼────────────┼─────────────┼─────────────┤\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │    985 │ 14988160 │ … │     1520.0 │     1521.0 │         143 │          15 │\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │    384 │  5841550 │ … │     1520.0 │     1521.0 │         103 │          90 │\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │     58 │   882110 │ … │     1520.0 │     1521.0 │         201 │          76 │\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │     49 │   745150 │ … │     1520.0 │     1521.0 │         289 │          68 │\n",
      "│ 2017-01-04 09:00:0…  │     1520.0 │    458 │  6963530 │ … │     1520.0 │     1521.0 │           8 │         117 │\n",
      "│ 2017-01-04 09:00:0…  │     1519.0 │    150 │  2279680 │ … │     1519.0 │     1520.0 │         220 │           3 │\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │     89 │  1352890 │ … │     1519.0 │     1521.0 │         202 │         131 │\n",
      "│ 2017-01-04 09:00:0…  │     1521.0 │     36 │   547270 │ … │     1519.0 │     1521.0 │         188 │         163 │\n",
      "│ 2017-01-04 09:00:0…  │     1519.0 │    101 │  1534420 │ … │     1519.0 │     1521.0 │         108 │         172 │\n",
      "│ 2017-01-04 09:00:0…  │     1520.0 │    343 │  5210610 │ … │     1518.0 │     1520.0 │         136 │          89 │\n",
      "│          ·           │        ·   │      · │      ·   │ · │        ·   │        ·   │          ·  │           · │\n",
      "│          ·           │        ·   │      · │      ·   │ · │        ·   │        ·   │          ·  │           · │\n",
      "│          ·           │        ·   │      · │      ·   │ · │        ·   │        ·   │          ·  │           · │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │      1 │    15350 │ … │     1535.0 │     1536.0 │         768 │         658 │\n",
      "│ 2017-01-04 10:41:4…  │     1536.0 │      2 │    30720 │ … │     1535.0 │     1536.0 │         769 │         657 │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │      3 │    46050 │ … │     1535.0 │     1536.0 │         770 │         658 │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │      0 │        0 │ … │     1535.0 │     1536.0 │         771 │         658 │\n",
      "│ 2017-01-04 10:41:4…  │     1536.0 │      2 │    30720 │ … │     1535.0 │     1536.0 │         781 │         656 │\n",
      "│ 2017-01-04 10:41:4…  │     1536.0 │      0 │        0 │ … │     1535.0 │     1536.0 │         782 │         675 │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │    800 │ 12280000 │ … │     1534.0 │     1535.0 │        1298 │          48 │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │      0 │        0 │ … │     1534.0 │     1535.0 │        1271 │         159 │\n",
      "│ 2017-01-04 10:41:4…  │     1535.0 │     44 │   675400 │ … │     1534.0 │     1535.0 │        1271 │         120 │\n",
      "│ 2017-01-04 10:41:5…  │     1535.0 │    120 │  1842000 │ … │     1535.0 │     1536.0 │          91 │         814 │\n",
      "├──────────────────────┴────────────┴────────┴──────────┴───┴────────────┴────────────┴─────────────┴─────────────┤\n",
      "│ ? rows (>9999 rows, 20 shown)                                                               9 columns (8 shown) │\n",
      "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import duckdb\n",
    "\n",
    "with duckdb.connect(\"datas/duckdb/finance_ticks_ctpfuture.db\") as conn:\n",
    "    with open(\"finance.duckdb.ticks.sql\") as f:\n",
    "        # conn.execute(\"drop table ticks_c;\")\n",
    "        sql = f.read().format(tablename='c', csv_path='datasources/c') # c 有3个G没有上传git了\n",
    "        conn.execute(sql)\n",
    "    conn.table('ticks_c').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "with duckdb.connect(\"datas/duckdb/test_finance.db\") as conn:\n",
    "    insert_sql = f\"INSERT INTO bars_daily_000001_SZ SELECT dt,name,_open,_high,_low,_close,volume,amount,preclose,net_profit_ttm,cashflow_ttm,equity,asset,debt,debttoasset,net_profit_q,pe_ttm,pb,mkt_cap,mkt_cap_ashare,vip_buy_amt,vip_sell_amt,inst_buy_amt,inst_sell_amt,mid_buy_amt,mid_sell_amt,indi_buy_amt,indi_sell_amt,master_net_flow_in,master2_net_flow_in,vip_net_flow_in,mid_net_flow_in,inst_net_flow_in,indi_net_flow_in,total_sell_amt,total_buy_amt,net_flow_in,turnover,free_shares,total_shares,maxupordown,maxupordown_at_open,lb_up_count,lb_down_count,close,open,high,low FROM 'datasources/data_append.csv' where symbol='000001.SZ';\"\n",
    "    conn.execute(insert_sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "with duckdb.connect(\"datas/duckdb/test_finance.db\") as conn:\n",
    "    # 删除不存在的行不会报错\n",
    "    insert_sql = f\"DELETE FROM bars_daily_000001_SZ WHERE dt = '2024-09-02 00:00:00';\"\n",
    "    conn.execute(insert_sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/test_finance.db...\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>name</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-07-25</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>10.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-07-26</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>10.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-07-29</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>10.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-07-30</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>10.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-07-31</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>10.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          dt  name   open\n",
       "0 2024-07-25  平安银行  10.12\n",
       "1 2024-07-26  平安银行  10.10\n",
       "2 2024-07-29  平安银行  10.02\n",
       "3 2024-07-30  平安银行  10.10\n",
       "4 2024-07-31  平安银行  10.08"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/test_finance.db\"]):\n",
    "    # df = qd.duckdb_get_array(\"test_finance\", 'bars_daily_000001_SZ').df()\n",
    "    # df = qd.duckdb_get_array(\"test_finance\", 'bars_daily_000001_SZ', attrs=['dt', 'name', 'open']).df()\n",
    "    # df = qd.duckdb_get_array(\"test_finance\", 'bars_daily_000001_SZ', attrs=['dt', 'name', 'open'], filter=\"dt>'2024-08-29 00:00:00' and open > 10.08\").df()\n",
    "    # 最后一行(所有列)\n",
    "    # df = qd.duckdb_get_array_last_rows(\"test_finance\", 'bars_daily_000001_sz').df()\n",
    "    # 最后一行(自定义列)\n",
    "    # df = qd.duckdb_get_array_last_rows(\"test_finance\", 'bars_daily_000001_SZ', attrs=['dt', 'name', 'open']).df()\n",
    "    # 最后5行\n",
    "    df = qd.duckdb_get_array_last_rows(\"test_finance\", 'bars_daily_000001_SZ', attrs=['dt', 'name', 'open'], filter=\"dt<'2024-08-01 00:00:00'\", N=5).df()\n",
    "    # df = qd.duckdb_get_array_last_rows(\"test_finance\", 'bars_daily_000001_SZ', N=1).df()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/test_finance.db...\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "rec.array([('1991-01-03T00:00:00', '深发展Ａ',  7.07077),\n",
       "           ('1991-01-04T00:00:00', '深发展Ａ',  7.03562),\n",
       "           ('1991-01-07T00:00:00', '深发展Ａ',  7.0729 ), ...,\n",
       "           ('2024-08-28T00:00:00', '平安银行', 10.41   ),\n",
       "           ('2024-08-29T00:00:00', '平安银行', 10.31   ),\n",
       "           ('2024-08-30T00:00:00', '平安银行', 10.11   )],\n",
       "          dtype=[('dt', '<M8[s]'), ('name', 'O'), ('open', '<f4')])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/test_finance.db\"]):\n",
    "    q = qd.duckdb_get_array(\"test_finance\", 'bars_daily_000001_SZ', attrs=['dt', 'name', 'open'])\n",
    "    recarray = qd.duckdb_fetchnumpy_to_recarray(q)\n",
    "recarray"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Benchmark\n",
    "\n",
    "以下为在大数据(全部5000+只股票的数据，一共5G)测试性能"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "5.32 ms ± 214 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "1.15 ms ± 59.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "540 µs ± 18.9 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "7.61 ms ± 99 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "1.51 ms ± 57.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "1.35 ms ± 35.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "7.93 ms ± 65 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.duckdb_get_array(\"finance\", 'bars_daily_000001_SZ').fetchnumpy()\n",
    "    %timeit  qd.duckdb_get_array(\"finance\", 'bars_daily_000002_SZ', attrs=['dt', 'name', 'open']).fetchnumpy()\n",
    "    %timeit  qd.duckdb_get_array(\"finance\", 'bars_daily_000003_SZ', attrs=['dt', 'name', 'open'], filter=\"dt>'2024-08-29 00:00:00' and open > 10.08\").fetchnumpy()\n",
    "    # # 最后一行(所有列)\n",
    "    %timeit  qd.duckdb_get_array_last_rows(\"finance\", 'bars_daily_000005_SZ').fetchnumpy()\n",
    "    # # 最后一行(自定义列)\n",
    "    %timeit  qd.duckdb_get_array_last_rows(\"finance\", 'bars_daily_000008_SZ', attrs=['dt', 'name', 'open']).fetchnumpy()\n",
    "    # # 最后5行\n",
    "    %timeit qd.duckdb_get_array_last_rows(\"finance\", 'bars_daily_000010_SZ', attrs=['dt', 'name', 'open'], N=5).fetchnumpy()\n",
    "    %timeit  qd.duckdb_get_array_last_rows(\"finance\", 'bars_daily_000001_SZ', N=5).fetchnumpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以下为 duckdb_attach 函数测试，分为\n",
    "- 本地文件\n",
    "- 局域网内共享文件\n",
    "- HTTP 文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "def test_conn01():\n",
    "    with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "        pass\n",
    "\n",
    "test_conn01()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach //DESKTOP-6NI30V4/bt_data/duckdb/finance.db...\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "def test_conn02():\n",
    "    with qd.open_duckdb(uris=[\"//DESKTOP-6NI30V4/bt_data/duckdb/finance.db\"]):\n",
    "        pass\n",
    "\n",
    "test_conn02()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "def test_conn03():\n",
    "    with qd.open_duckdb(extensions=('httpfs',), uris=[\"http://192.168.2.40:9001/finance.db\"]):\n",
    "        pass\n",
    "\n",
    "test_conn03()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "以下为 fetchxxx() 函数测试\n",
    "\n",
    "- 测试拉取一只股票所有数据. fetchnumpy 效率是最高的, 其次是 fetchdf, fetchall 最差"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6.33 ms ± 44.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.duckdb_get_array(conn, \"finance\", 'bars_daily_000001_SZ').fetchnumpy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.2 ms ± 654 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.duckdb_get_array(\"finance\", 'bars_daily_000001_SZ').fetchdf()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "71.1 ms ± 16.9 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.duckdb_get_array(\"finance\", 'bars_daily_000001_SZ').fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 拉取少量数据（6个字段，5天的数据）fetchnumpy 和 fetchall 效率最优, 其次是 fetchdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_microsec = 1711929600000000 # datetime(2024,4,1)\n",
    "till_microsec = 1712448000000000 # datetime(2024,4,7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "658 µs ± 9.76 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.get_data_ndarray(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "1.18 ms ± 189 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.get_data_df(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "768 µs ± 20 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    %timeit qd.get_data_recarray_s(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对获取 recarray 函数`duckdb_fetchrecarray`进行测试：\n",
    "\n",
    "- 底层用 fetchall. 大数据上效率最低"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "大数据:\n",
      "62.4 ms ± 983 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "小数据:\n",
      "765 µs ± 15.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "import numpy as np\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    print(\"大数据:\")\n",
    "    %timeit qd.get_data_recarray_onlyfortest(\"finance\", 'bars_daily_000001_SZ')\n",
    "    print(\"小数据:\")\n",
    "    %timeit qd.get_data_recarray_onlyfortest(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 底层用 fetchnumpy 效率均衡"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "大数据:\n",
      "36.4 ms ± 2.21 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "小数据:\n",
      "764 µs ± 17.6 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    print(\"大数据:\")\n",
    "    %timeit qd.get_data_recarray_s(\"finance\", 'bars_daily_000001_SZ')\n",
    "    print(\"小数据:\")\n",
    "    %timeit qd.get_data_recarray_s(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 底层用 fetchdf. 大数据上效率最高"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duckdb attach datas/duckdb/finance.db...\n",
      "大数据:\n",
      "24.6 ms ± 387 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n",
      "小数据:\n",
      "1.46 ms ± 27.9 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "duckdb closing...\n",
      "duckdb closed\n"
     ]
    }
   ],
   "source": [
    "import quantdata as qd\n",
    "\n",
    "with qd.open_duckdb(uris=[\"datas/duckdb/finance.db\"]):\n",
    "    print(\"大数据:\")\n",
    "    %timeit qd.get_data_recarray_l(\"finance\", 'bars_daily_000001_SZ')\n",
    "    print(\"小数据:\")\n",
    "    %timeit qd.get_data_recarray_l(\"finance\", 'bars_daily_000001_SZ', [\"dt\", \"open\", \"high\", \"low\", \"close\", \"maxupordown\"], start_microsec=start_microsec, till_microsec=till_microsec)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}