guofu
/
dolphin-dev
1
0
Fork 0
Code Issues 3 Pull Requests Packages Projects Releases 1 Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
dolphin-dev/ipynb/ddb_daily_factor.ipynb

3142 lines
108 KiB
Raw Permalink Normal View History Unescape

Updates
2 years ago
{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"id": "a4915372-8508-45ce-ba31-8aef7c5b0ef1",
"metadata": {},
"outputs": [],
"source": [
"import dolphindb as ddb\n",
"\n",
"sess = ddb.session('localhost', 8848)\n",
"sess.login('admin', '123456')"
]
},
{
"cell_type": "code",
"execution_count": 108,
"id": "ebd3b848-e0ce-4d0f-94f7-78a687040e80",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>dow</th>\n",
" <th>moy</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-06</td>\n",
" <td>4</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-10</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>95</th>\n",
" <td>000400.SZ</td>\n",
" <td>2011-01-25</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>96</th>\n",
" <td>000400.SZ</td>\n",
" <td>2011-01-26</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>97</th>\n",
" <td>000400.SZ</td>\n",
" <td>2011-01-27</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>98</th>\n",
" <td>000400.SZ</td>\n",
" <td>2011-01-28</td>\n",
" <td>4</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>99</th>\n",
" <td>000400.SZ</td>\n",
" <td>2011-01-31</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 4 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate dow moy\n",
"0 000400.SZ 2006-01-04 2 1\n",
"1 000400.SZ 2006-01-05 3 1\n",
"2 000400.SZ 2006-01-06 4 1\n",
"3 000400.SZ 2006-01-09 0 1\n",
"4 000400.SZ 2006-01-10 1 1\n",
".. ... ... ... ...\n",
"95 000400.SZ 2011-01-25 1 1\n",
"96 000400.SZ 2011-01-26 2 1\n",
"97 000400.SZ 2011-01-27 3 1\n",
"98 000400.SZ 2011-01-28 4 1\n",
"99 000400.SZ 2011-01-31 0 1\n",
"\n",
"[100 rows x 4 columns]"
]
},
"execution_count": 108,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select top 100 code, m_nDate, dayOfWeek(m_nDate) as dow, monthOfYear(m_nDate) as moy \n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" where monthOfYear(m_nDate) = 1\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 92,
"id": "ae07f021-409d-4b8b-8676-ff2a0f54bb34",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>vol_20</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2776841</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2776842</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-05</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2776843</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-06</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2776844</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-09</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2776845</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-10</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2780773</th>\n",
" <td>600000.SH</td>\n",
" <td>2022-08-04</td>\n",
" <td>0.291758</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2780774</th>\n",
" <td>600000.SH</td>\n",
" <td>2022-08-05</td>\n",
" <td>0.289024</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2780775</th>\n",
" <td>600000.SH</td>\n",
" <td>2022-08-08</td>\n",
" <td>0.283952</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2780776</th>\n",
" <td>600000.SH</td>\n",
" <td>2022-08-09</td>\n",
" <td>0.270085</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2780777</th>\n",
" <td>600000.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.256963</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3937 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate vol_20\n",
"2776841 600000.SH 2006-01-04 NaN\n",
"2776842 600000.SH 2006-01-05 NaN\n",
"2776843 600000.SH 2006-01-06 NaN\n",
"2776844 600000.SH 2006-01-09 NaN\n",
"2776845 600000.SH 2006-01-10 NaN\n",
"... ... ... ...\n",
"2780773 600000.SH 2022-08-04 0.291758\n",
"2780774 600000.SH 2022-08-05 0.289024\n",
"2780775 600000.SH 2022-08-08 0.283952\n",
"2780776 600000.SH 2022-08-09 0.270085\n",
"2780777 600000.SH 2022-08-10 0.256963\n",
"\n",
"[3937 rows x 3 columns]"
]
},
"execution_count": 92,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
" vol_20 = select code, m_nDate, mstdp(close, 20, 9) as vol_20\n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" context by code;\n",
" vol_20\n",
"\"\"\")\n",
"df[df['code'] == '600000.SH']"
]
},
{
"cell_type": "code",
"execution_count": 79,
"id": "cffabf93-81d3-4075-b80a-1c761ccd95b6",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>rank_vol_20</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000515.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000558.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000602.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000731.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>688272.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.901734</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>688320.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.985756</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>688739.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.707267</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>688777.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.960776</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.021263</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate rank_vol_20\n",
"0 000400.SZ 2006-01-04 NaN\n",
"1 000515.SZ 2006-01-04 NaN\n",
"2 000558.SZ 2006-01-04 NaN\n",
"3 000602.SZ 2006-01-04 NaN\n",
"4 000731.SZ 2006-01-04 NaN\n",
"... ... ... ...\n",
"10403520 688272.SH 2022-08-10 0.901734\n",
"10403521 688320.SH 2022-08-10 0.985756\n",
"10403522 688739.SH 2022-08-10 0.707267\n",
"10403523 688777.SH 2022-08-10 0.960776\n",
"10403524 830964.NE 2022-08-10 0.021263\n",
"\n",
"[10403525 rows x 3 columns]"
]
},
"execution_count": 79,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
" rank_vol_20 = select code, m_nDate, rank(vol_20, tiesMethod='average', percent=true)\n",
" from vol_20 context by m_nDate;\n",
" rank_vol_20\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 80,
"id": "f10dd713-e516-48bc-a232-f1570eef03ff",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>rank_vol_20</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2295890</th>\n",
" <td>000400.SZ</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.932731</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2295891</th>\n",
" <td>000558.SZ</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.191422</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2295892</th>\n",
" <td>000602.SZ</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.367946</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2295893</th>\n",
" <td>000731.SZ</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.252370</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2295894</th>\n",
" <td>000752.SZ</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.734537</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2298104</th>\n",
" <td>600586.SH</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.126862</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2298105</th>\n",
" <td>600660.SH</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.031603</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2298106</th>\n",
" <td>601299.SH</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.127765</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2298107</th>\n",
" <td>601788.SH</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.419413</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2298108</th>\n",
" <td>601988.SH</td>\n",
" <td>2012-01-05</td>\n",
" <td>0.000451</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>2219 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate rank_vol_20\n",
"2295890 000400.SZ 2012-01-05 0.932731\n",
"2295891 000558.SZ 2012-01-05 0.191422\n",
"2295892 000602.SZ 2012-01-05 0.367946\n",
"2295893 000731.SZ 2012-01-05 0.252370\n",
"2295894 000752.SZ 2012-01-05 0.734537\n",
"... ... ... ...\n",
"2298104 600586.SH 2012-01-05 0.126862\n",
"2298105 600660.SH 2012-01-05 0.031603\n",
"2298106 601299.SH 2012-01-05 0.127765\n",
"2298107 601788.SH 2012-01-05 0.419413\n",
"2298108 601988.SH 2012-01-05 0.000451\n",
"\n",
"[2219 rows x 3 columns]"
]
},
"execution_count": 80,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[df['m_nDate'] == '2012-01-05']"
]
},
{
"cell_type": "code",
"execution_count": 84,
"id": "972934f5-35c7-4874-b990-4ce0f9d58965",
"metadata": {},
"outputs": [],
"source": [
"df = sess.run(\"\"\"\n",
" corr_5 = select code, m_nDate, mcorr(high, vol, 5) as corr_5\n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" context by code;\n",
" corr_5\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 91,
"id": "d3442f93-3beb-40c1-92c1-7f2d2d0e8ec3",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>delta_corr_5</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>5873882</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-11</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873883</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-12</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873884</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-13</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873885</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-16</td>\n",
" <td>0.111578</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873886</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-17</td>\n",
" <td>0.053048</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873887</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-18</td>\n",
" <td>0.680933</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873888</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-19</td>\n",
" <td>-0.530191</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873889</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-20</td>\n",
" <td>-0.283952</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873890</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-23</td>\n",
" <td>-0.295373</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5873891</th>\n",
" <td>600000.SH</td>\n",
" <td>2006-01-24</td>\n",
" <td>-0.105135</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" code m_nDate delta_corr_5\n",
"5873882 600000.SH 2006-01-11 NaN\n",
"5873883 600000.SH 2006-01-12 NaN\n",
"5873884 600000.SH 2006-01-13 NaN\n",
"5873885 600000.SH 2006-01-16 0.111578\n",
"5873886 600000.SH 2006-01-17 0.053048\n",
"5873887 600000.SH 2006-01-18 0.680933\n",
"5873888 600000.SH 2006-01-19 -0.530191\n",
"5873889 600000.SH 2006-01-20 -0.283952\n",
"5873890 600000.SH 2006-01-23 -0.295373\n",
"5873891 600000.SH 2006-01-24 -0.105135"
]
},
"execution_count": 91,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
" delta_corr_5 = select code, m_nDate, mfirst(corr_5, 5) - corr_5 as delta_corr_5\n",
" from corr_5 context by code;\n",
" delta_corr_5\n",
"\"\"\")\n",
"df[df['code'] == '600000.SH'].iloc[5:15]"
]
},
{
"cell_type": "code",
"execution_count": 93,
"id": "b70d0329-0f82-4fef-a6e1-faf2068e85f4",
"metadata": {},
"outputs": [],
"source": [
"df = sess.run(\"\"\"\n",
" alpha101_22 = select code, m_nDate, delta_corr_5 * rank_vol_20 as alpha101_22\n",
" from ej(rank_vol_20, delta_corr_5, `code`m_nDate);\n",
" alpha101_22\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 94,
"id": "4fbb58eb-58fa-40d9-a0e8-32e9b4868629",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>alpha101_22</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000515.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000558.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000602.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000731.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>688272.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.280800</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>688320.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.464768</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>688739.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.054898</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>688777.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.427098</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-10</td>\n",
" <td>-0.016451</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate alpha101_22\n",
"0 000400.SZ 2006-01-04 NaN\n",
"1 000515.SZ 2006-01-04 NaN\n",
"2 000558.SZ 2006-01-04 NaN\n",
"3 000602.SZ 2006-01-04 NaN\n",
"4 000731.SZ 2006-01-04 NaN\n",
"... ... ... ...\n",
"10403520 688272.SH 2022-08-10 0.280800\n",
"10403521 688320.SH 2022-08-10 0.464768\n",
"10403522 688739.SH 2022-08-10 0.054898\n",
"10403523 688777.SH 2022-08-10 0.427098\n",
"10403524 830964.NE 2022-08-10 -0.016451\n",
"\n",
"[10403525 rows x 3 columns]"
]
},
"execution_count": 94,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "f211d667-4932-457e-9051-667607f8a105",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>month_of_year</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-06</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-10</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>95</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-06</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>96</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-07</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>97</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-08</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>98</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-09</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>99</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-12</td>\n",
" <td>6</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate month_of_year\n",
"0 000400.SZ 2006-01-04 1\n",
"1 000400.SZ 2006-01-05 1\n",
"2 000400.SZ 2006-01-06 1\n",
"3 000400.SZ 2006-01-09 1\n",
"4 000400.SZ 2006-01-10 1\n",
".. ... ... ...\n",
"95 000400.SZ 2006-06-06 6\n",
"96 000400.SZ 2006-06-07 6\n",
"97 000400.SZ 2006-06-08 6\n",
"98 000400.SZ 2006-06-09 6\n",
"99 000400.SZ 2006-06-12 6\n",
"\n",
"[100 rows x 3 columns]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select top 100 code, m_nDate, monthOfYear(m_nDate) as month_of_year \n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 97,
"id": "fc62191a-94f6-4172-a2a2-39907d94e6b5",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>trend_with_turnover</th>\n",
" <th>trend_with_amount</th>\n",
" <th>abs_trend_with_turnover</th>\n",
" <th>abs_trend_with_amount</th>\n",
" <th>alpha101_22</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" <td>0.082185</td>\n",
" <td>0.101943</td>\n",
" <td>0.082185</td>\n",
" <td>0.101943</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>NaN</td>\n",
" <td>0.006255</td>\n",
" <td>0.024839</td>\n",
" <td>0.006255</td>\n",
" <td>0.024839</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-06</td>\n",
" <td>NaN</td>\n",
" <td>-0.004377</td>\n",
" <td>-0.011194</td>\n",
" <td>0.004377</td>\n",
" <td>0.011194</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>NaN</td>\n",
" <td>0.029549</td>\n",
" <td>0.066196</td>\n",
" <td>0.029549</td>\n",
" <td>0.066196</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-10</td>\n",
" <td>NaN</td>\n",
" <td>0.011716</td>\n",
" <td>0.013202</td>\n",
" <td>0.011716</td>\n",
" <td>0.013202</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>95</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-06</td>\n",
" <td>0.121066</td>\n",
" <td>0.021408</td>\n",
" <td>-0.019479</td>\n",
" <td>-0.021408</td>\n",
" <td>0.019479</td>\n",
" </tr>\n",
" <tr>\n",
" <th>96</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-07</td>\n",
" <td>0.091073</td>\n",
" <td>0.099845</td>\n",
" <td>-0.084317</td>\n",
" <td>-0.099845</td>\n",
" <td>0.084317</td>\n",
" </tr>\n",
" <tr>\n",
" <th>97</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-08</td>\n",
" <td>-0.336859</td>\n",
" <td>0.007474</td>\n",
" <td>-0.006340</td>\n",
" <td>-0.007474</td>\n",
" <td>0.006340</td>\n",
" </tr>\n",
" <tr>\n",
" <th>98</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-09</td>\n",
" <td>-0.453049</td>\n",
" <td>-0.005057</td>\n",
" <td>0.002774</td>\n",
" <td>-0.005057</td>\n",
" <td>0.002774</td>\n",
" </tr>\n",
" <tr>\n",
" <th>99</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-12</td>\n",
" <td>-0.244429</td>\n",
" <td>-0.007322</td>\n",
" <td>0.006228</td>\n",
" <td>-0.007322</td>\n",
" <td>0.006228</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 7 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate trend_with_turnover trend_with_amount \\\n",
"0 000400.SZ 2006-01-04 NaN 0.082185 \n",
"1 000400.SZ 2006-01-05 NaN 0.006255 \n",
"2 000400.SZ 2006-01-06 NaN -0.004377 \n",
"3 000400.SZ 2006-01-09 NaN 0.029549 \n",
"4 000400.SZ 2006-01-10 NaN 0.011716 \n",
".. ... ... ... ... \n",
"95 000400.SZ 2006-06-06 0.121066 0.021408 \n",
"96 000400.SZ 2006-06-07 0.091073 0.099845 \n",
"97 000400.SZ 2006-06-08 -0.336859 0.007474 \n",
"98 000400.SZ 2006-06-09 -0.453049 -0.005057 \n",
"99 000400.SZ 2006-06-12 -0.244429 -0.007322 \n",
"\n",
" abs_trend_with_turnover abs_trend_with_amount alpha101_22 \n",
"0 0.101943 0.082185 0.101943 \n",
"1 0.024839 0.006255 0.024839 \n",
"2 -0.011194 0.004377 0.011194 \n",
"3 0.066196 0.029549 0.066196 \n",
"4 0.013202 0.011716 0.013202 \n",
".. ... ... ... \n",
"95 -0.019479 -0.021408 0.019479 \n",
"96 -0.084317 -0.099845 0.084317 \n",
"97 -0.006340 -0.007474 0.006340 \n",
"98 0.002774 -0.005057 0.002774 \n",
"99 0.006228 -0.007322 0.006228 \n",
"\n",
"[100 rows x 7 columns]"
]
},
"execution_count": 97,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select top 100 * \n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_factor\")\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "081d51c7-f9d5-4493-bc16-a03945d1e4c2",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>open</th>\n",
" <th>high</th>\n",
" <th>low</th>\n",
" <th>close</th>\n",
" <th>IsZt</th>\n",
" <th>IsT</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-10</td>\n",
" <td>3.17</td>\n",
" <td>3.20</td>\n",
" <td>3.11</td>\n",
" <td>3.13</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-11</td>\n",
" <td>3.02</td>\n",
" <td>3.11</td>\n",
" <td>2.82</td>\n",
" <td>2.83</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-12</td>\n",
" <td>2.85</td>\n",
" <td>2.86</td>\n",
" <td>2.62</td>\n",
" <td>2.77</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-15</td>\n",
" <td>2.80</td>\n",
" <td>2.87</td>\n",
" <td>2.73</td>\n",
" <td>2.73</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-16</td>\n",
" <td>2.73</td>\n",
" <td>2.81</td>\n",
" <td>2.60</td>\n",
" <td>2.67</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-17</td>\n",
" <td>2.71</td>\n",
" <td>2.74</td>\n",
" <td>2.57</td>\n",
" <td>2.66</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-18</td>\n",
" <td>2.67</td>\n",
" <td>2.67</td>\n",
" <td>2.46</td>\n",
" <td>2.50</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-19</td>\n",
" <td>2.42</td>\n",
" <td>2.59</td>\n",
" <td>2.42</td>\n",
" <td>2.58</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-22</td>\n",
" <td>2.84</td>\n",
" <td>2.84</td>\n",
" <td>2.84</td>\n",
" <td>2.84</td>\n",
" <td>1</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-23</td>\n",
" <td>3.12</td>\n",
" <td>3.12</td>\n",
" <td>2.84</td>\n",
" <td>3.12</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-24</td>\n",
" <td>3.13</td>\n",
" <td>3.43</td>\n",
" <td>3.02</td>\n",
" <td>3.43</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-25</td>\n",
" <td>3.70</td>\n",
" <td>3.77</td>\n",
" <td>3.61</td>\n",
" <td>3.77</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-26</td>\n",
" <td>4.15</td>\n",
" <td>4.15</td>\n",
" <td>4.15</td>\n",
" <td>4.15</td>\n",
" <td>1</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-29</td>\n",
" <td>4.30</td>\n",
" <td>4.57</td>\n",
" <td>4.25</td>\n",
" <td>4.57</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-10-30</td>\n",
" <td>4.58</td>\n",
" <td>5.03</td>\n",
" <td>4.46</td>\n",
" <td>5.03</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-11-05</td>\n",
" <td>5.53</td>\n",
" <td>5.53</td>\n",
" <td>5.53</td>\n",
" <td>5.53</td>\n",
" <td>1</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-11-06</td>\n",
" <td>6.08</td>\n",
" <td>6.08</td>\n",
" <td>6.08</td>\n",
" <td>6.08</td>\n",
" <td>1</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-11-07</td>\n",
" <td>6.45</td>\n",
" <td>6.69</td>\n",
" <td>6.33</td>\n",
" <td>6.69</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-11-08</td>\n",
" <td>7.25</td>\n",
" <td>7.36</td>\n",
" <td>6.98</td>\n",
" <td>7.36</td>\n",
" <td>1</td>\n",
" <td>True</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>000622.SZ</td>\n",
" <td>2018-11-09</td>\n",
" <td>7.38</td>\n",
" <td>8.10</td>\n",
" <td>7.26</td>\n",
" <td>7.90</td>\n",
" <td>0</td>\n",
" <td>False</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" code m_nDate open high low close IsZt IsT\n",
"0 000622.SZ 2018-10-10 3.17 3.20 3.11 3.13 0 False\n",
"1 000622.SZ 2018-10-11 3.02 3.11 2.82 2.83 0 False\n",
"2 000622.SZ 2018-10-12 2.85 2.86 2.62 2.77 0 False\n",
"3 000622.SZ 2018-10-15 2.80 2.87 2.73 2.73 0 False\n",
"4 000622.SZ 2018-10-16 2.73 2.81 2.60 2.67 0 False\n",
"5 000622.SZ 2018-10-17 2.71 2.74 2.57 2.66 0 False\n",
"6 000622.SZ 2018-10-18 2.67 2.67 2.46 2.50 0 False\n",
"7 000622.SZ 2018-10-19 2.42 2.59 2.42 2.58 0 False\n",
"8 000622.SZ 2018-10-22 2.84 2.84 2.84 2.84 1 False\n",
"9 000622.SZ 2018-10-23 3.12 3.12 2.84 3.12 1 True\n",
"10 000622.SZ 2018-10-24 3.13 3.43 3.02 3.43 1 True\n",
"11 000622.SZ 2018-10-25 3.70 3.77 3.61 3.77 1 True\n",
"12 000622.SZ 2018-10-26 4.15 4.15 4.15 4.15 1 False\n",
"13 000622.SZ 2018-10-29 4.30 4.57 4.25 4.57 1 True\n",
"14 000622.SZ 2018-10-30 4.58 5.03 4.46 5.03 1 True\n",
"15 000622.SZ 2018-11-05 5.53 5.53 5.53 5.53 1 False\n",
"16 000622.SZ 2018-11-06 6.08 6.08 6.08 6.08 1 False\n",
"17 000622.SZ 2018-11-07 6.45 6.69 6.33 6.69 1 True\n",
"18 000622.SZ 2018-11-08 7.25 7.36 6.98 7.36 1 True\n",
"19 000622.SZ 2018-11-09 7.38 8.10 7.26 7.90 0 False"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select code, m_nDate, open, high, low, close, IsZt, \\\n",
" (IsZt and high=close and low!=close) as IsT \\\n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\") \n",
" where code='000622.SZ' and m_nDate>=2018.10.10 and m_nDate<=2018.11.10\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "14c51fe0-3791-4f5b-8ccc-bfd43f0f5be9",
"metadata": {},
"outputs": [],
"source": [
"sess.run(\"\"\"\n",
" t1 = select code, m_nDate, amount/vol as vwap from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\") \n",
" where code='000400.SZ' and m_nDate>=2014.01.04 and m_nDate<=2014.02.04;\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 20,
"id": "29bc4089-4df1-4bea-800f-d01e893d08bd",
"metadata": {},
"outputs": [],
"source": [
"sess.run(\"\"\"\n",
" t2 = select code, m_nDate, vwap from loadTable(\"dfs://daily_stock_ts\", \"hft_daily_factor\")\n",
" where code='000400.SZ' and m_nDate>=2014.01.04 and m_nDate<=2014.02.04\n",
"\"\"\") "
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b98b12d4-1068-4c98-8374-bbc1adce01a3",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>vwap</th>\n",
" <th>t2_vwap</th>\n",
" <th>vwap_sub</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-06</td>\n",
" <td>30.628533</td>\n",
" <td>30.982086</td>\n",
" <td>-0.353552</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-07</td>\n",
" <td>33.004000</td>\n",
" <td>32.612976</td>\n",
" <td>0.391024</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-08</td>\n",
" <td>32.908724</td>\n",
" <td>32.610945</td>\n",
" <td>0.297780</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-09</td>\n",
" <td>33.563315</td>\n",
" <td>33.736282</td>\n",
" <td>-0.172967</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-10</td>\n",
" <td>32.012339</td>\n",
" <td>32.273132</td>\n",
" <td>-0.260793</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-13</td>\n",
" <td>32.301440</td>\n",
" <td>32.113160</td>\n",
" <td>0.188280</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-14</td>\n",
" <td>32.495571</td>\n",
" <td>32.781237</td>\n",
" <td>-0.285666</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-15</td>\n",
" <td>33.791427</td>\n",
" <td>33.750742</td>\n",
" <td>0.040686</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-16</td>\n",
" <td>34.695708</td>\n",
" <td>34.871143</td>\n",
" <td>-0.175435</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-17</td>\n",
" <td>33.910330</td>\n",
" <td>33.978306</td>\n",
" <td>-0.067975</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-20</td>\n",
" <td>33.707389</td>\n",
" <td>33.731507</td>\n",
" <td>-0.024118</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-21</td>\n",
" <td>33.338210</td>\n",
" <td>33.663293</td>\n",
" <td>-0.325083</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-22</td>\n",
" <td>34.046440</td>\n",
" <td>33.657111</td>\n",
" <td>0.389329</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-23</td>\n",
" <td>34.028093</td>\n",
" <td>34.028356</td>\n",
" <td>-0.000263</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-24</td>\n",
" <td>33.919916</td>\n",
" <td>33.879026</td>\n",
" <td>0.040890</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-27</td>\n",
" <td>34.104556</td>\n",
" <td>33.949844</td>\n",
" <td>0.154712</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-28</td>\n",
" <td>33.926302</td>\n",
" <td>34.179352</td>\n",
" <td>-0.253050</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-29</td>\n",
" <td>34.046293</td>\n",
" <td>33.917053</td>\n",
" <td>0.129240</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>000400.SZ</td>\n",
" <td>2014-01-30</td>\n",
" <td>34.041070</td>\n",
" <td>34.464937</td>\n",
" <td>-0.423867</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" code m_nDate vwap t2_vwap vwap_sub\n",
"0 000400.SZ 2014-01-06 30.628533 30.982086 -0.353552\n",
"1 000400.SZ 2014-01-07 33.004000 32.612976 0.391024\n",
"2 000400.SZ 2014-01-08 32.908724 32.610945 0.297780\n",
"3 000400.SZ 2014-01-09 33.563315 33.736282 -0.172967\n",
"4 000400.SZ 2014-01-10 32.012339 32.273132 -0.260793\n",
"5 000400.SZ 2014-01-13 32.301440 32.113160 0.188280\n",
"6 000400.SZ 2014-01-14 32.495571 32.781237 -0.285666\n",
"7 000400.SZ 2014-01-15 33.791427 33.750742 0.040686\n",
"8 000400.SZ 2014-01-16 34.695708 34.871143 -0.175435\n",
"9 000400.SZ 2014-01-17 33.910330 33.978306 -0.067975\n",
"10 000400.SZ 2014-01-20 33.707389 33.731507 -0.024118\n",
"11 000400.SZ 2014-01-21 33.338210 33.663293 -0.325083\n",
"12 000400.SZ 2014-01-22 34.046440 33.657111 0.389329\n",
"13 000400.SZ 2014-01-23 34.028093 34.028356 -0.000263\n",
"14 000400.SZ 2014-01-24 33.919916 33.879026 0.040890\n",
"15 000400.SZ 2014-01-27 34.104556 33.949844 0.154712\n",
"16 000400.SZ 2014-01-28 33.926302 34.179352 -0.253050\n",
"17 000400.SZ 2014-01-29 34.046293 33.917053 0.129240\n",
"18 000400.SZ 2014-01-30 34.041070 34.464937 -0.423867"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select *, t1.vwap-t2.vwap from ej(t1, t2, `code`m_nDate)\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 51,
"id": "b0543222-4201-4ca7-8a38-2f561adecd76",
"metadata": {},
"outputs": [
{
"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>winsorize</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0.191026</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>0.082927</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>0.143954</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>-0.060634</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0.014164</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3932</th>\n",
" <td>0.014144</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3933</th>\n",
" <td>0.042313</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3934</th>\n",
" <td>-0.020979</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3935</th>\n",
" <td>0.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3936</th>\n",
" <td>-0.021067</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3937 rows × 1 columns</p>\n",
"</div>"
],
"text/plain": [
" winsorize\n",
"0 0.191026\n",
"1 0.082927\n",
"2 0.143954\n",
"3 -0.060634\n",
"4 0.014164\n",
"... ...\n",
"3932 0.014144\n",
"3933 0.042313\n",
"3934 -0.020979\n",
"3935 0.000000\n",
"3936 -0.021067\n",
"\n",
"[3937 rows x 1 columns]"
]
},
"execution_count": 51,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" t1 = select winsorize(PctChg/20, 0.05) from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\") where code=\"600000.SH\";\n",
" t1;\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "4d0ac236-d2b3-4724-9f89-040d85e95221",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>amount</th>\n",
" <th>amount_rank</th>\n",
" <th>amount_with_trend</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>3.265679e+07</td>\n",
" <td>0.410321</td>\n",
" <td>0.661152</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000515.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>9.116335e+06</td>\n",
" <td>0.177665</td>\n",
" <td>0.462941</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000558.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>2.417575e+06</td>\n",
" <td>-0.231810</td>\n",
" <td>0.209710</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000602.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>1.767682e+07</td>\n",
" <td>0.314721</td>\n",
" <td>0.705630</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000731.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>1.402217e+07</td>\n",
" <td>0.272420</td>\n",
" <td>0.675770</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>688272.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>7.012509e+07</td>\n",
" <td>-0.014727</td>\n",
" <td>0.271909</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>688320.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>1.067048e+08</td>\n",
" <td>0.101442</td>\n",
" <td>0.561355</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>688739.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>1.035707e+07</td>\n",
" <td>-0.442122</td>\n",
" <td>0.030941</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>688777.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>4.529292e+08</td>\n",
" <td>0.394336</td>\n",
" <td>0.751877</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-10</td>\n",
" <td>5.583561e+05</td>\n",
" <td>-0.493409</td>\n",
" <td>0.002081</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 5 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate amount amount_rank amount_with_trend\n",
"0 000400.SZ 2006-01-04 3.265679e+07 0.410321 0.661152\n",
"1 000515.SZ 2006-01-04 9.116335e+06 0.177665 0.462941\n",
"2 000558.SZ 2006-01-04 2.417575e+06 -0.231810 0.209710\n",
"3 000602.SZ 2006-01-04 1.767682e+07 0.314721 0.705630\n",
"4 000731.SZ 2006-01-04 1.402217e+07 0.272420 0.675770\n",
"... ... ... ... ... ...\n",
"10403520 688272.SH 2022-08-10 7.012509e+07 -0.014727 0.271909\n",
"10403521 688320.SH 2022-08-10 1.067048e+08 0.101442 0.561355\n",
"10403522 688739.SH 2022-08-10 1.035707e+07 -0.442122 0.030941\n",
"10403523 688777.SH 2022-08-10 4.529292e+08 0.394336 0.751877\n",
"10403524 830964.NE 2022-08-10 5.583561e+05 -0.493409 0.002081\n",
"\n",
"[10403525 rows x 5 columns]"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
" select \n",
" code, m_nDate, \n",
" amount, \n",
" rank(amount,tiesMethod='average', percent=true)-0.5 as amount_rank,\n",
" rank(amount,tiesMethod='average', percent=true)-0.5 * winsorize(abs(PctChg)/10, 0.02) as amount_with_trend\n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" context by m_nDate\n",
"\"\"\")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 88,
"id": "b0d7d22f-6d80-45ed-b685-0cbc0daccd13",
"metadata": {},
"outputs": [],
"source": [
"df = sess.run(\"\"\"\n",
" tableInsert(\n",
" loadTable(\"dfs://daily_stock_ts\", \"daily_factor\"),\n",
" select \n",
" code, m_nDate, \n",
" rank(amount,tiesMethod='average', percent=true)-0.5 * winsorize(abs(PctChg), 0.02) \n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" context by m_nDate\n",
" )\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 118,
"id": "a72d9b1f-a84a-463f-87b8-1841d51afd5e",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>trend_with_amount</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.101943</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>0.024839</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-06</td>\n",
" <td>-0.011194</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>0.066196</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-10</td>\n",
" <td>0.013202</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>95</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-06</td>\n",
" <td>-0.019479</td>\n",
" </tr>\n",
" <tr>\n",
" <th>96</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-07</td>\n",
" <td>-0.084317</td>\n",
" </tr>\n",
" <tr>\n",
" <th>97</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-08</td>\n",
" <td>-0.006340</td>\n",
" </tr>\n",
" <tr>\n",
" <th>98</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-09</td>\n",
" <td>0.002774</td>\n",
" </tr>\n",
" <tr>\n",
" <th>99</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-06-12</td>\n",
" <td>0.006228</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate trend_with_amount\n",
"0 000400.SZ 2006-01-04 0.101943\n",
"1 000400.SZ 2006-01-05 0.024839\n",
"2 000400.SZ 2006-01-06 -0.011194\n",
"3 000400.SZ 2006-01-09 0.066196\n",
"4 000400.SZ 2006-01-10 0.013202\n",
".. ... ... ...\n",
"95 000400.SZ 2006-06-06 -0.019479\n",
"96 000400.SZ 2006-06-07 -0.084317\n",
"97 000400.SZ 2006-06-08 -0.006340\n",
"98 000400.SZ 2006-06-09 0.002774\n",
"99 000400.SZ 2006-06-12 0.006228\n",
"\n",
"[100 rows x 3 columns]"
]
},
"execution_count": 118,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" select top 100 * from loadTable(\"dfs://daily_stock_ts\", \"daily_factor\")\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 87,
"id": "bbf2d32b-db27-4f3d-820e-8d53ff936b2d",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>amount</th>\n",
" <th>rank_amount_sub</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>3.265679e+07</td>\n",
" <td>0.410321</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1182</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>2.582027e+07</td>\n",
" <td>0.338525</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2402</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-06</td>\n",
" <td>3.477456e+07</td>\n",
" <td>0.361201</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3634</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>3.356378e+07</td>\n",
" <td>0.374590</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4854</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-10</td>\n",
" <td>5.437912e+07</td>\n",
" <td>0.438322</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10379267</th>\n",
" <td>000400.SZ</td>\n",
" <td>2022-08-04</td>\n",
" <td>1.660664e+09</td>\n",
" <td>0.487822</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10384112</th>\n",
" <td>000400.SZ</td>\n",
" <td>2022-08-05</td>\n",
" <td>1.430263e+09</td>\n",
" <td>0.480821</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10388961</th>\n",
" <td>000400.SZ</td>\n",
" <td>2022-08-08</td>\n",
" <td>9.427016e+08</td>\n",
" <td>0.464359</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10393815</th>\n",
" <td>000400.SZ</td>\n",
" <td>2022-08-09</td>\n",
" <td>9.085981e+08</td>\n",
" <td>0.464985</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10398670</th>\n",
" <td>000400.SZ</td>\n",
" <td>2022-08-10</td>\n",
" <td>1.074877e+09</td>\n",
" <td>0.471164</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>3786 rows × 4 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate amount rank_amount_sub\n",
"0 000400.SZ 2006-01-04 3.265679e+07 0.410321\n",
"1182 000400.SZ 2006-01-05 2.582027e+07 0.338525\n",
"2402 000400.SZ 2006-01-06 3.477456e+07 0.361201\n",
"3634 000400.SZ 2006-01-09 3.356378e+07 0.374590\n",
"4854 000400.SZ 2006-01-10 5.437912e+07 0.438322\n",
"... ... ... ... ...\n",
"10379267 000400.SZ 2022-08-04 1.660664e+09 0.487822\n",
"10384112 000400.SZ 2022-08-05 1.430263e+09 0.480821\n",
"10388961 000400.SZ 2022-08-08 9.427016e+08 0.464359\n",
"10393815 000400.SZ 2022-08-09 9.085981e+08 0.464985\n",
"10398670 000400.SZ 2022-08-10 1.074877e+09 0.471164\n",
"\n",
"[3786 rows x 4 columns]"
]
},
"execution_count": 87,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[df['code'] == '000400.SZ']"
]
},
{
"cell_type": "code",
"execution_count": 47,
"id": "0548beb0-9cc7-4c27-9383-2519e72b62a6",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>trend_with_amount</th>\n",
" <th>trend_with_turnover</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>2.461929</td>\n",
" <td>1.984772</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000515.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.762978</td>\n",
" <td>1.693085</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000558.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>-0.271123</td>\n",
" <td>0.042549</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000602.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.686664</td>\n",
" <td>0.895247</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000731.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.526584</td>\n",
" <td>0.304176</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>688272.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>-0.062845</td>\n",
" <td>2.001800</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>688320.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>0.081330</td>\n",
" <td>0.256045</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>688739.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>-0.238192</td>\n",
" <td>-0.179157</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>688777.SH</td>\n",
" <td>2022-08-10</td>\n",
" <td>1.123532</td>\n",
" <td>0.242664</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-10</td>\n",
" <td>-0.000000</td>\n",
" <td>-0.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 4 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate trend_with_amount trend_with_turnover\n",
"0 000400.SZ 2006-01-04 2.461929 1.984772\n",
"1 000515.SZ 2006-01-04 0.762978 1.693085\n",
"2 000558.SZ 2006-01-04 -0.271123 0.042549\n",
"3 000602.SZ 2006-01-04 0.686664 0.895247\n",
"4 000731.SZ 2006-01-04 0.526584 0.304176\n",
"... ... ... ... ...\n",
"10403520 688272.SH 2022-08-10 -0.062845 2.001800\n",
"10403521 688320.SH 2022-08-10 0.081330 0.256045\n",
"10403522 688739.SH 2022-08-10 -0.238192 -0.179157\n",
"10403523 688777.SH 2022-08-10 1.123532 0.242664\n",
"10403524 830964.NE 2022-08-10 -0.000000 -0.000000\n",
"\n",
"[10403525 rows x 4 columns]"
]
},
"execution_count": 47,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" f = select \n",
" code, m_nDate, \n",
" (rank(amount, tiesMethod='average', percent=true) - 0.5) * abs(winsorize(PctChg, 0.01)) as trend_with_amount,\n",
" (rank((amount/MarketValues), tiesMethod='average', percent=true) - 0.5) * abs(winsorize(PctChg, 0.01)) as trend_with_turnover\n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\")\n",
" context by m_nDate\n",
" f;\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "b604dd48-2869-41d8-a001-275ab0b49594",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>rank_PctChg_sub</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-03</td>\n",
" <td>0.461697</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.241123</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>-0.124425</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-08</td>\n",
" <td>0.399528</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>0.108267</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-03</td>\n",
" <td>-0.027355</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-04</td>\n",
" <td>-0.070112</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-07</td>\n",
" <td>0.050077</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-08</td>\n",
" <td>-0.027355</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-09</td>\n",
" <td>-0.027355</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 3 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate rank_PctChg_sub\n",
"0 000400.SZ 2006-01-03 0.461697\n",
"1 000400.SZ 2006-01-04 0.241123\n",
"2 000400.SZ 2006-01-05 -0.124425\n",
"3 000400.SZ 2006-01-08 0.399528\n",
"4 000400.SZ 2006-01-09 0.108267\n",
"... ... ... ...\n",
"10403520 830964.NE 2022-08-03 -0.027355\n",
"10403521 830964.NE 2022-08-04 -0.070112\n",
"10403522 830964.NE 2022-08-07 0.050077\n",
"10403523 830964.NE 2022-08-08 -0.027355\n",
"10403524 830964.NE 2022-08-09 -0.027355\n",
"\n",
"[10403525 rows x 3 columns]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sess.run(\"\"\"\n",
" r = select \n",
" code, m_nDate-1 as m_nDate, \n",
" rank(PctChg, percent=true)-0.5 \n",
" from loadTable(\"dfs://daily_stock_ts\", \"daily_kline\");\n",
" r;\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 95,
"id": "daab613f-9e6b-4f75-bfbe-80a2293912c3",
"metadata": {},
"outputs": [
{
"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>code</th>\n",
" <th>m_nDate</th>\n",
" <th>rank_PctChg_sub</th>\n",
" <th>alpha101_22_m_nDate</th>\n",
" <th>alpha101_22</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-03</td>\n",
" <td>0.461697</td>\n",
" <td>NaT</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-04</td>\n",
" <td>0.241123</td>\n",
" <td>2006-01-04</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-05</td>\n",
" <td>-0.124425</td>\n",
" <td>2006-01-05</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-08</td>\n",
" <td>0.399528</td>\n",
" <td>2006-01-06</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>000400.SZ</td>\n",
" <td>2006-01-09</td>\n",
" <td>0.108267</td>\n",
" <td>2006-01-09</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403520</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-03</td>\n",
" <td>-0.027355</td>\n",
" <td>2022-08-03</td>\n",
" <td>0.008355</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403521</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-04</td>\n",
" <td>-0.070112</td>\n",
" <td>2022-08-04</td>\n",
" <td>0.015653</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403522</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-07</td>\n",
" <td>0.050077</td>\n",
" <td>2022-08-05</td>\n",
" <td>0.014966</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403523</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-08</td>\n",
" <td>-0.027355</td>\n",
" <td>2022-08-08</td>\n",
" <td>-0.034349</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10403524</th>\n",
" <td>830964.NE</td>\n",
" <td>2022-08-09</td>\n",
" <td>-0.027355</td>\n",
" <td>2022-08-09</td>\n",
" <td>-0.021269</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10403525 rows × 5 columns</p>\n",
"</div>"
],
"text/plain": [
" code m_nDate rank_PctChg_sub alpha101_22_m_nDate \\\n",
"0 000400.SZ 2006-01-03 0.461697 NaT \n",
"1 000400.SZ 2006-01-04 0.241123 2006-01-04 \n",
"2 000400.SZ 2006-01-05 -0.124425 2006-01-05 \n",
"3 000400.SZ 2006-01-08 0.399528 2006-01-06 \n",
"4 000400.SZ 2006-01-09 0.108267 2006-01-09 \n",
"... ... ... ... ... \n",
"10403520 830964.NE 2022-08-03 -0.027355 2022-08-03 \n",
"10403521 830964.NE 2022-08-04 -0.070112 2022-08-04 \n",
"10403522 830964.NE 2022-08-07 0.050077 2022-08-05 \n",
"10403523 830964.NE 2022-08-08 -0.027355 2022-08-08 \n",
"10403524 830964.NE 2022-08-09 -0.027355 2022-08-09 \n",
"\n",
" alpha101_22 \n",
"0 NaN \n",
"1 NaN \n",
"2 NaN \n",
"3 NaN \n",
"4 NaN \n",
"... ... \n",
"10403520 0.008355 \n",
"10403521 0.015653 \n",
"10403522 0.014966 \n",
"10403523 -0.034349 \n",
"10403524 -0.021269 \n",
"\n",
"[10403525 rows x 5 columns]"
]
},
"execution_count": 95,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
" select * from aj(r, alpha101_22, `code`m_nDate)\n",
"\"\"\")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 96,
"id": "ea2c353c-2e74-46f1-86c9-48322e9f9296",
"metadata": {},
"outputs": [
{
"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>rank_PctChg_sub</th>\n",
" <th>alpha101_22</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>rank_PctChg_sub</th>\n",
" <td>1.000000</td>\n",
" <td>-0.006789</td>\n",
" </tr>\n",
" <tr>\n",
" <th>alpha101_22</th>\n",
" <td>-0.006789</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" rank_PctChg_sub alpha101_22\n",
"rank_PctChg_sub 1.000000 -0.006789\n",
"alpha101_22 -0.006789 1.000000"
]
},
"execution_count": 96,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[[\"rank_PctChg_sub\", \"alpha101_22\"]].dropna().corr()"
]
},
{
"cell_type": "code",
"execution_count": 54,
"id": "aac33fb2-d06b-4456-81a2-cf8476a13d4d",
"metadata": {},
"outputs": [],
"source": [
"df = df.set_index([\"m_nDate\"])[['rank_PctChg_sub', 'trend_with_amount', 'trend_with_turnover']].dropna()"
]
},
{
"cell_type": "code",
"execution_count": 55,
"id": "bc7447a7-be59-481b-b15a-d2ff59355135",
"metadata": {},
"outputs": [
{
"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>rank_PctChg_sub</th>\n",
" <th>trend_with_amount</th>\n",
" <th>trend_with_turnover</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>rank_PctChg_sub</th>\n",
" <td>1.000000</td>\n",
" <td>-0.041950</td>\n",
" <td>-0.032851</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trend_with_amount</th>\n",
" <td>-0.041950</td>\n",
" <td>1.000000</td>\n",
" <td>0.622215</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trend_with_turnover</th>\n",
" <td>-0.032851</td>\n",
" <td>0.622215</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" rank_PctChg_sub trend_with_amount trend_with_turnover\n",
"rank_PctChg_sub 1.000000 -0.041950 -0.032851\n",
"trend_with_amount -0.041950 1.000000 0.622215\n",
"trend_with_turnover -0.032851 0.622215 1.000000"
]
},
"execution_count": 55,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.corr(method=\"pearson\")"
]
},
{
"cell_type": "code",
"execution_count": 60,
"id": "259aad61-545d-48b2-80d1-7588e2b1b95e",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"-0.0417603527848196"
]
},
"execution_count": 60,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from scipy.spatial import distance\n",
"1 - distance.cosine(df['rank_PctChg_sub'], df['trend_with_amount'])"
]
},
{
"cell_type": "code",
"execution_count": 62,
"id": "84a31edf-a9cd-47bb-9f2b-384a45479c2d",
"metadata": {},
"outputs": [],
"source": [
"s_corr = df[[\"rank_PctChg_sub\", \"trend_with_amount\"]]\\\n",
" .groupby(\"m_nDate\").apply(lambda _df : 1 - distance.cosine(_df['rank_PctChg_sub'], _df['trend_with_amount']))"
]
},
{
"cell_type": "code",
"execution_count": 65,
"id": "0574a132-0744-412d-a2b3-e7d5b78c3d7a",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0.3412639405204461"
]
},
"execution_count": 65,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"sum(s_corr > 0) / len(s_corr)"
]
},
{
"cell_type": "code",
"execution_count": 131,
"id": "faac8d09-0e8c-409e-85e9-0cb2d567e7b6",
"metadata": {},
"outputs": [
{
"ename": "RuntimeError",
"evalue": "<Exception> in run: Server response: 'select code as symbol,m_nDate as date,open,close,high,low,vol as volume,amount,cjbs,yclose,PctChg,amount / vol as vwap,FloatShares,MarketValues,trend_with_turnover,trend_with_amount,k_skew,k_advrev,k_tail30trade,k_vpcorr,k_largeorder_ret,factor from lj(lj(loadTable(\"dfs://daily_stock_ts\", \"daily_kline\"),loadTable(\"dfs://daily_stock_ts\", \"daily_factor\"),[\"code\",\"m_nDate\"]),loadTable(\"dfs://daily_stock_ts\", \"hft_daily_factor\"),[\"code\",\"m_nDate\"]) where partition(code, 0) order by m_nDate asc => Multiple-table join does not support partitioned table except the first table!' script: '\n select code as symbol, m_nDate as date, open, close, high, low, vol as volume, amount, cjbs, yclose, PctChg, amount/vol as vwap, FloatShares, MarketValues, trend_with_turnover, trend_with_amount, k_skew, k_advrev, k_tail30trade, k_vpcorr, k_largeorder_ret, factor as factor\n from lj(lj(loadTable('dfs://daily_stock_ts', 'daily_kline'), loadTable('dfs://daily_stock_ts', 'daily_factor'), `code`m_nDate), loadTable('dfs://daily_stock_ts', 'hft_daily_factor'), `code`m_nDate) where partition(code, 0)\n order by m_nDate asc;\n'",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mRuntimeError\u001b[0m Traceback (most recent call last)",
"Input \u001b[0;32mIn [131]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43msess\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrun\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\n\u001b[1;32m 2\u001b[0m \u001b[38;5;124;43m select code as symbol, m_nDate as date, open, close, high, low, vol as volume, amount, cjbs, yclose, PctChg, amount/vol as vwap, FloatShares, MarketValues, trend_with_turnover, trend_with_amount, k_skew, k_advrev, k_tail30trade, k_vpcorr, k_largeorder_ret, factor as factor\u001b[39;49m\n\u001b[1;32m 3\u001b[0m \u001b[38;5;124;43m from lj(lj(loadTable(\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdfs://daily_stock_ts\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m, \u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdaily_kline\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m), loadTable(\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdfs://daily_stock_ts\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m, \u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdaily_factor\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m), `code`m_nDate), loadTable(\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mdfs://daily_stock_ts\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m, \u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mhft_daily_factor\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m), `code`m_nDate) where partition(code, 0)\u001b[39;49m\n\u001b[1;32m 4\u001b[0m \u001b[38;5;124;43m order by m_nDate asc;\u001b[39;49m\n\u001b[1;32m 5\u001b[0m \u001b[38;5;124;43m\"\"\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
"File \u001b[0;32m~/.venv/tinysoft/lib/python3.8/site-packages/dolphindb/session.py:161\u001b[0m, in \u001b[0;36msession.run\u001b[0;34m(self, script, *args, **kwargs)\u001b[0m\n\u001b[1;32m 159\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfetchSize\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01min\u001b[39;00m kwargs\u001b[38;5;241m.\u001b[39mkeys():\n\u001b[1;32m 160\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m BlockReader(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcpp\u001b[38;5;241m.\u001b[39mrunBlock(script, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs))\n\u001b[0;32m--> 161\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcpp\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrun\u001b[49m\u001b[43m(\u001b[49m\u001b[43mscript\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
"\u001b[0;31mRuntimeError\u001b[0m: <Exception> in run: Server response: 'select code as symbol,m_nDate as date,open,close,high,low,vol as volume,amount,cjbs,yclose,PctChg,amount / vol as vwap,FloatShares,MarketValues,trend_with_turnover,trend_with_amount,k_skew,k_advrev,k_tail30trade,k_vpcorr,k_largeorder_ret,factor from lj(lj(loadTable(\"dfs://daily_stock_ts\", \"daily_kline\"),loadTable(\"dfs://daily_stock_ts\", \"daily_factor\"),[\"code\",\"m_nDate\"]),loadTable(\"dfs://daily_stock_ts\", \"hft_daily_factor\"),[\"code\",\"m_nDate\"]) where partition(code, 0) order by m_nDate asc => Multiple-table join does not support partitioned table except the first table!' script: '\n select code as symbol, m_nDate as date, open, close, high, low, vol as volume, amount, cjbs, yclose, PctChg, amount/vol as vwap, FloatShares, MarketValues, trend_with_turnover, trend_with_amount, k_skew, k_advrev, k_tail30trade, k_vpcorr, k_largeorder_ret, factor as factor\n from lj(lj(loadTable('dfs://daily_stock_ts', 'daily_kline'), loadTable('dfs://daily_stock_ts', 'daily_factor'), `code`m_nDate), loadTable('dfs://daily_stock_ts', 'hft_daily_factor'), `code`m_nDate) where partition(code, 0)\n order by m_nDate asc;\n'"
]
}
],
"source": [
"sess.run(\"\"\"\n",
" select code as symbol, m_nDate as date, open, close, high, low, vol as volume, amount, cjbs, yclose, PctChg, amount/vol as vwap, FloatShares, MarketValues, trend_with_turnover, trend_with_amount, k_skew, k_advrev, k_tail30trade, k_vpcorr, k_largeorder_ret, factor as factor\n",
" from lj(lj(loadTable('dfs://daily_stock_ts', 'daily_kline'), loadTable('dfs://daily_stock_ts', 'daily_factor'), `code`m_nDate), loadTable('dfs://daily_stock_ts', 'hft_daily_factor'), `code`m_nDate) where partition(code, 0)\n",
" order by m_nDate asc;\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": 39,
"id": "7f05d056-4369-4f30-9f3e-47505799532e",
"metadata": {},
"outputs": [
{
"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>abs</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1.491228</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>3.027605</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>0.388937</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>9.987086</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>1.056751</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9053861</th>\n",
" <td>0.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9053862</th>\n",
" <td>0.298507</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9053863</th>\n",
" <td>0.299401</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9053864</th>\n",
" <td>0.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9053865</th>\n",
" <td>0.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>9053866 rows × 1 columns</p>\n",
"</div>"
],
"text/plain": [
" abs\n",
"0 1.491228\n",
"1 3.027605\n",
"2 0.388937\n",
"3 9.987086\n",
"4 1.056751\n",
"... ...\n",
"9053861 0.000000\n",
"9053862 0.298507\n",
"9053863 0.299401\n",
"9053864 0.000000\n",
"9053865 0.000000\n",
"\n",
"[9053866 rows x 1 columns]"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = sess.run(\"\"\"\n",
"select abs(winsorize(PctChg, 0.01)) from lj(loadTable('dfs://daily_stock_ts', 'daily_kline'), loadTable('dfs://daily_stock_ts', 'daily_factor'), `code`m_nDate) \n",
"where m_nDate > 2010.01.01\n",
"\"\"\")\n",
"df"
]
},
{
"cell_type": "code",
"execution_count": 40,
"id": "90805827-5fe0-416c-9c6f-58473cbad48b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"array([[<AxesSubplot:title={'center':'abs'}>]], dtype=object)"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEICAYAAABPgw/pAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8qNh9FAAAACXBIWXMAAAsTAAALEwEAmpwYAAAUEElEQVR4nO3df5Bd5X3f8ffHSLWJNoYmcjdE4IgpxKkrxrG1BRqmmZWpZ2Tjgf7AKa5rWx5SpR4T/yhuDf4DTz3TKZkWJza4ZlQg4ERFOJgWFWhcD2aLydTUEiEWEiaRMQ4SGBlkhJdQHJVv/9jjzmZnpXt3dXev9tn3a+bOnh/POef7iN0Pzz577rmpKiRJS9+rhl2AJGkwDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6FInyaYkDwy7Dmm+DHRJaoSBLkmNMNC17CS5Isl3kvwoyZ4k//Cv7s51SQ4l+XaS86ft2JTk8e647yZ5zxDKl45oxbALkIbgO8DfA74PvAv4/SRndPvOAW4HVgP/CLgjyenAy8DngL9TVY8lOQX4mUWvXDqKoY7Qk9yU5ECSR/ps/2vdiGp3kv+80PWpTVX1B1X1VFW9UlW3AX8GnN3tPgD8TlX9ZbfvMeCCbt8rwLokJ1bV01W1e/Grl45s2FMuNwMb+2mY5EzgSuC8qvrbwEcXriy1LMn7kjyc5PkkzwPrmBqRA+yvv/rEuu8BP19VLwL/BPgXwNNJ7k7yS4tauNTDUAO9qu4HDk7fluRvJvnDJDuTfH3aD80/Bz5fVT/sjj2wyOWqAUl+AfhPwGXAz1bVycAjQLoma5Jk2iGvB54CqKqvVNXbgFOAb3fnkY4bwx6hz2YL8JtVtR74OPAfu+2/CPxikj9K8o0kfY3spRlWAQX8ACDJB5gaof/E3wA+nGRlkncBfwu4J8lokouSrGJqPn2SqSkY6bhxXP1RNMkI8CvAH0wbJL26+7oCOBMYB04F7k9yVlU9v8hlagmrqj1JrgH+F1OB/EXgj6Y1eZCp77NngWeAi6vque6PoP+ya1/Aw8AHF7F0qacM+wMukqwF7qqqdUleCzxWVafM0u564MGq+t1u/V7giqr65qIWLEnHqeNqyqWqXgC+2/2qS6a8qdv9X5kanZNkNVNTMI8PoUxJOi4N+7bFW5n61fcNSfYluRR4D3Bpkj8BdgMXdc2/AjyXZA9wH/Cvquq5YdQtScejoU+5SJIG47iacpEkzd/Q7nJZvXp1rV27dl7Hvvjii6xatWqwBR3n7PPyYJ+Xh2Pp886dO5+tqtfNtq9noCd5DXA/U7cPrgBur6pPzWizCfj3wP5u03VVdcPRzrt27Vp27NjRu/pZTExMMD4+Pq9jlyr7vDzY5+XhWPqc5HtH2tfPCP1l4K1VNZlkJfBAkv9eVd+Y0e62qrpsXhVKko5Zz0Dvnmsx2a2u7F7+JVWSjjN93eWS5ARgJ3AGU89T+cSM/ZuAf8fU26n/FPhYVT05y3k2A5sBRkdH12/btm1eRU9OTjIyMjKvY5cq+7w82Ofl4Vj6vGHDhp1VNTbrzqrq+wWczNQ94OtmbP9Z4NXd8m8AX+t1rvXr19d83XffffM+dqmyz8uDfV4ejqXPwI46Qq7O6bbFmnpuyn3MeORtVT1XVS93qzcA6+dyXknSsesZ6Elel+TkbvlE4G1MPTp0epvpz165EHh0gDVKkvrQz10upwC3dPPorwK+VFV3Jfk0U0P/7Uw9bvRC4DBTzzfftFAFS5Jm189dLt8C3jzL9qumLV/J1KcJSZKGxLf+S1IjjqsPuOjXrv2H2HTF3UO59hNXX9C7kSQNgSN0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1omegJ3lNkv+d5E+S7E7yb2Zp8+oktyXZm+TBJGsXpFpJ0hH1M0J/GXhrVb0J+GVgY5JzZ7S5FPhhVZ0B/DbwWwOtUpLUU89ArymT3erK7lUzml0E3NIt3w6cnyQDq1KS1FOqZmbzLI2SE4CdwBnA56vqEzP2PwJsrKp93fp3gHOq6tkZ7TYDmwFGR0fXb9u2bV5FHzh4iGdemtehx+ysNScN5bqTk5OMjIwM5drDYp+XB/s8Nxs2bNhZVWOz7VvRzwmq6v8Cv5zkZOC/JFlXVY/MtZCq2gJsARgbG6vx8fG5ngKAa7feyTW7+ip94J54z/hQrjsxMcF8/72WKvu8PNjnwZnTXS5V9TxwH7Bxxq79wGkASVYAJwHPDaA+SVKf+rnL5XXdyJwkJwJvA749o9l24P3d8sXA16qfuRxJ0sD0M29xCnBLN4/+KuBLVXVXkk8DO6pqO3Aj8HtJ9gIHgUsWrGJJ0qx6BnpVfQt48yzbr5q2/H+Adw22NEnSXPhOUUlqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RG9Az0JKcluS/JniS7k3xkljbjSQ4lebh7XbUw5UqSjmRFH20OA5dX1UNJfhrYmeSrVbVnRruvV9U7B1+iJKkfPUfoVfV0VT3ULf8IeBRYs9CFSZLmJlXVf+NkLXA/sK6qXpi2fRz4MrAPeAr4eFXtnuX4zcBmgNHR0fXbtm2bV9EHDh7imZfmdegxO2vNSUO57uTkJCMjI0O59rDY5+XBPs/Nhg0bdlbV2Gz7+g70JCPA/wT+bVXdMWPfa4FXqmoyyTuAz1bVmUc739jYWO3YsaOva8907dY7uWZXP7NFg/fE1RcM5boTExOMj48P5drDYp+XB/s8N0mOGOh93eWSZCVTI/CtM8McoKpeqKrJbvkeYGWS1fOqVpI0L/3c5RLgRuDRqvrMEdr8XNeOJGd3531ukIVKko6un3mL84D3AruSPNxt+yTweoCquh64GPhgksPAS8AlNZfJeUnSMesZ6FX1AJAeba4DrhtUUZKkufOdopLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1IjhPLJwCVt7xd1Due7NG1cN5bqSlg5H6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqRM9AT3JakvuS7EmyO8lHZmmTJJ9LsjfJt5K8ZWHKlSQdST9PWzwMXF5VDyX5aWBnkq9W1Z5pbd4OnNm9zgG+0H2VJC2SniP0qnq6qh7qln8EPAqsmdHsIuCLNeUbwMlJThl4tZKkI0pV9d84WQvcD6yrqhembb8LuLqqHujW7wU+UVU7Zhy/GdgMMDo6un7btm3zKvrAwUM889K8Dl2yTj/pBEZGRoZdxqKanJy0z8uAfZ6bDRs27Kyqsdn29f0BF0lGgC8DH50e5nNRVVuALQBjY2M1Pj4+n9Nw7dY7uWbX8vpsjps3rmK+/15L1cTEhH1eBuzz4PR1l0uSlUyF+daqumOWJvuB06atn9ptkyQtkn7ucglwI/BoVX3mCM22A+/r7nY5FzhUVU8PsE5JUg/9zFucB7wX2JXk4W7bJ4HXA1TV9cA9wDuAvcBfAB8YeKWSpKPqGejdHzrTo00BHxpUUZKkufOdopLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmN6BnoSW5KciDJI0fYP57kUJKHu9dVgy9TktTLij7a3AxcB3zxKG2+XlXvHEhFkqR56TlCr6r7gYOLUIsk6Rikqno3StYCd1XVuln2jQNfBvYBTwEfr6rdRzjPZmAzwOjo6Ppt27bNq+gDBw/xzEvzOnTJOv2kExgZGRl2GYtqcnLSPi8D9nluNmzYsLOqxmbbN4hAfy3wSlVNJnkH8NmqOrPXOcfGxmrHjh0
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"df.hist()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e5869db1-4134-426a-a8a0-782cc27ff198",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"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.8.13"
}
},
"nbformat": 4,
"nbformat_minor": 5
}