历史文章附件列表/（数据科学学习手札163）ibis：极具潜力的Python数据分析框架/（数据科学学习手札163）ibis：极具潜力的Python数据分析框架.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a1bde17d-19cf-409e-a5f0-5f4e68aa0442",
   "metadata": {},
   "source": [
    "## 2.2 ibis主要功能"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6427e9b6-70dd-4d24-b5f5-25a404abd005",
   "metadata": {},
   "source": [
    "### 2.2.1 构建具有便携性的分析逻辑"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "63f8a296-870f-4e95-9664-86fe4af43c14",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# 生成测试用数据并导出为parquet格式\n",
    "(\n",
    "    pd\n",
    "    .DataFrame(\n",
    "        {\n",
    "            '类别': np.random.choice(list('abcdef'), 10000000),\n",
    "            '数值': np.random.uniform(0, 100, 10000000)\n",
    "        }\n",
    "    )\n",
    "    .assign(数值=lambda df: df['数值'].round(3))\n",
    "    .to_parquet('demo.parquet')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c163cd4d-4697-4904-8a36-fe7553dea164",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "ibis.options.interactive = True # 开启适用jupyter环境的交互模式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7e37cbf6-abd9-4146-b264-d65447b62775",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 172 ms\n",
      "Wall time: 161 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃<span style=\"font-weight: bold\"> 类别   </span>┃<span style=\"font-weight: bold\"> 组内样… </span>┃<span style=\"font-weight: bold\"> 组内平均… </span>┃<span style=\"font-weight: bold\"> 组内中… </span>┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">string</span> │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">int64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span> │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">a     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668194</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.980634</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.977</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">b     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1667820</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.983055</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.994</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">c     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665020</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.030410</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.036</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">d     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1664831</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.021382</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.013</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">e     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665392</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.985976</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.965</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">f     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668743</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.014333</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.018</span> │\n",
       "└────────┴─────────┴───────────┴─────────┘\n",
       "</pre>\n"
      ],
      "text/plain": [
       "┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃\u001b[1m \u001b[0m\u001b[1m类别\u001b[0m\u001b[1m  \u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内样…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内平均…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内中…\u001b[0m\u001b[1m \u001b[0m┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ \u001b[2mstring\u001b[0m │ \u001b[2mint64\u001b[0m   │ \u001b[2mfloat64\u001b[0m   │ \u001b[2mfloat64\u001b[0m │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ \u001b[32ma     \u001b[0m │ \u001b[1;36m1668194\u001b[0m │ \u001b[1;36m49.980634\u001b[0m │  \u001b[1;36m49.977\u001b[0m │\n",
       "│ \u001b[32mb     \u001b[0m │ \u001b[1;36m1667820\u001b[0m │ \u001b[1;36m49.983055\u001b[0m │  \u001b[1;36m49.994\u001b[0m │\n",
       "│ \u001b[32mc     \u001b[0m │ \u001b[1;36m1665020\u001b[0m │ \u001b[1;36m50.030410\u001b[0m │  \u001b[1;36m50.036\u001b[0m │\n",
       "│ \u001b[32md     \u001b[0m │ \u001b[1;36m1664831\u001b[0m │ \u001b[1;36m50.021382\u001b[0m │  \u001b[1;36m50.013\u001b[0m │\n",
       "│ \u001b[32me     \u001b[0m │ \u001b[1;36m1665392\u001b[0m │ \u001b[1;36m49.985976\u001b[0m │  \u001b[1;36m49.965\u001b[0m │\n",
       "│ \u001b[32mf     \u001b[0m │ \u001b[1;36m1668743\u001b[0m │ \u001b[1;36m50.014333\u001b[0m │  \u001b[1;36m50.018\u001b[0m │\n",
       "└────────┴─────────┴───────────┴─────────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# 默认使用DuckDB后端\n",
    "demo_t = ibis.read_parquet('demo.parquet')\n",
    "\n",
    "# 执行简单的分组运算\n",
    "\n",
    "expr = (\n",
    "    demo_t\n",
    "    .group_by('类别')\n",
    "    .aggregate(\n",
    "        组内样本数=lambda t: t.数值.count(),\n",
    "        组内平均数=lambda t: t.数值.mean(),\n",
    "        组内中位数=lambda t: t.数值.median(),\n",
    "    )\n",
    "    .order_by('类别')\n",
    ")\n",
    "expr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "67ab6e31-6574-4986-a48e-8ca8e67b7a28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 1.83 s\n",
      "Wall time: 1.54 s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃<span style=\"font-weight: bold\"> 类别   </span>┃<span style=\"font-weight: bold\"> 组内样… </span>┃<span style=\"font-weight: bold\"> 组内平均… </span>┃<span style=\"font-weight: bold\"> 组内中… </span>┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">string</span> │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">int64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span> │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">a     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668194</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.980634</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.977</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">b     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1667820</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.983055</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.994</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">c     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665020</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.030410</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.036</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">d     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1664831</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.021382</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.013</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">e     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665392</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.985976</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.965</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">f     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668743</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.014333</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.018</span> │\n",
       "└────────┴─────────┴───────────┴─────────┘\n",
       "</pre>\n"
      ],
      "text/plain": [
       "┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃\u001b[1m \u001b[0m\u001b[1m类别\u001b[0m\u001b[1m  \u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内样…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内平均…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内中…\u001b[0m\u001b[1m \u001b[0m┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ \u001b[2mstring\u001b[0m │ \u001b[2mint64\u001b[0m   │ \u001b[2mfloat64\u001b[0m   │ \u001b[2mfloat64\u001b[0m │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ \u001b[32ma     \u001b[0m │ \u001b[1;36m1668194\u001b[0m │ \u001b[1;36m49.980634\u001b[0m │  \u001b[1;36m49.977\u001b[0m │\n",
       "│ \u001b[32mb     \u001b[0m │ \u001b[1;36m1667820\u001b[0m │ \u001b[1;36m49.983055\u001b[0m │  \u001b[1;36m49.994\u001b[0m │\n",
       "│ \u001b[32mc     \u001b[0m │ \u001b[1;36m1665020\u001b[0m │ \u001b[1;36m50.030410\u001b[0m │  \u001b[1;36m50.036\u001b[0m │\n",
       "│ \u001b[32md     \u001b[0m │ \u001b[1;36m1664831\u001b[0m │ \u001b[1;36m50.021382\u001b[0m │  \u001b[1;36m50.013\u001b[0m │\n",
       "│ \u001b[32me     \u001b[0m │ \u001b[1;36m1665392\u001b[0m │ \u001b[1;36m49.985976\u001b[0m │  \u001b[1;36m49.965\u001b[0m │\n",
       "│ \u001b[32mf     \u001b[0m │ \u001b[1;36m1668743\u001b[0m │ \u001b[1;36m50.014333\u001b[0m │  \u001b[1;36m50.018\u001b[0m │\n",
       "└────────┴─────────┴───────────┴─────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# 切换到pandas后端\n",
    "con = ibis.pandas.connect()\n",
    "demo_t = con.read_parquet('demo.parquet')\n",
    "\n",
    "# 执行简单的分组运算\n",
    "\n",
    "expr = (\n",
    "    demo_t\n",
    "    .group_by('类别')\n",
    "    .aggregate(\n",
    "        组内样本数=lambda t: t.数值.count(),\n",
    "        组内平均数=lambda t: t.数值.mean(),\n",
    "        组内中位数=lambda t: t.数值.median(),\n",
    "    )\n",
    "    .order_by('类别')\n",
    ")\n",
    "expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17058ee4-7e53-4208-ab27-120f1d563757",
   "metadata": {},
   "source": [
    "### 2.2.2 充分搭配Python与SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "524e3d72-db0e-48d9-b5da-bf5e178823a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 默认使用DuckDB后端\n",
    "demo_t = ibis.read_parquet('demo.parquet')\n",
    "\n",
    "expr = (\n",
    "    demo_t\n",
    "    .group_by('类别')\n",
    "    .aggregate(\n",
    "        组内样本数=lambda t: t.数值.count(),\n",
    "        组内平均数=lambda t: t.数值.mean(),\n",
    "        组内中位数=lambda t: t.数值.median(),\n",
    "    )\n",
    "    .order_by('类别')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf511e6d-8ab5-477d-b603-6fd524c67107",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "```sql\n",
       "SELECT\n",
       "  *\n",
       "FROM (\n",
       "  SELECT\n",
       "    \"t0\".\"类别\",\n",
       "    COUNT(\"t0\".\"数值\") AS \"组内样本数\",\n",
       "    AVG(\"t0\".\"数值\") AS \"组内平均数\",\n",
       "    MEDIAN(\"t0\".\"数值\") AS \"组内中位数\"\n",
       "  FROM \"ibis_read_parquet_qvyjomcxznf5hfsdjtgvenbsiq\" AS \"t0\"\n",
       "  GROUP BY\n",
       "    1\n",
       ") AS \"t1\"\n",
       "ORDER BY\n",
       "  \"t1\".\"类别\" ASC\n",
       "```"
      ],
      "text/plain": [
       "\u001b[34mSELECT\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m*\u001b[37m\u001b[39;49;00m\n",
       "\u001b[34mFROM\u001b[39;49;00m\u001b[37m \u001b[39;49;00m(\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m\u001b[34mSELECT\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m    \u001b[39;49;00m\u001b[33m\"t0\"\u001b[39;49;00m.\u001b[33m\"类别\"\u001b[39;49;00m,\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m    \u001b[39;49;00m\u001b[34mCOUNT\u001b[39;49;00m(\u001b[33m\"t0\"\u001b[39;49;00m.\u001b[33m\"数值\"\u001b[39;49;00m)\u001b[37m \u001b[39;49;00m\u001b[34mAS\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"组内样本数\"\u001b[39;49;00m,\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m    \u001b[39;49;00m\u001b[34mAVG\u001b[39;49;00m(\u001b[33m\"t0\"\u001b[39;49;00m.\u001b[33m\"数值\"\u001b[39;49;00m)\u001b[37m \u001b[39;49;00m\u001b[34mAS\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"组内平均数\"\u001b[39;49;00m,\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m    \u001b[39;49;00mMEDIAN(\u001b[33m\"t0\"\u001b[39;49;00m.\u001b[33m\"数值\"\u001b[39;49;00m)\u001b[37m \u001b[39;49;00m\u001b[34mAS\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"组内中位数\"\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m\u001b[34mFROM\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"ibis_read_parquet_qvyjomcxznf5hfsdjtgvenbsiq\"\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[34mAS\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"t0\"\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m\u001b[34mGROUP\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[34mBY\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m    \u001b[39;49;00m\u001b[34m1\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       ")\u001b[37m \u001b[39;49;00m\u001b[34mAS\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"t1\"\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[34mORDER\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[34mBY\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m\u001b[33m\"t1\"\u001b[39;49;00m.\u001b[33m\"类别\"\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[34mASC\u001b[39;49;00m\u001b[37m\u001b[39;49;00m"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转换为sql\n",
    "ibis.to_sql(expr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "17ccb8d3-04e3-4d3f-8c00-d8e2b269685e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "```sql\n",
       "SELECT\n",
       "  *\n",
       "FROM \"ibis_read_parquet_qvyjomcxznf5hfsdjtgvenbsiq\"\n",
       "```"
      ],
      "text/plain": [
       "\u001b[34mSELECT\u001b[39;49;00m\u001b[37m\u001b[39;49;00m\n",
       "\u001b[37m  \u001b[39;49;00m*\u001b[37m\u001b[39;49;00m\n",
       "\u001b[34mFROM\u001b[39;49;00m\u001b[37m \u001b[39;49;00m\u001b[33m\"ibis_read_parquet_qvyjomcxznf5hfsdjtgvenbsiq\"\u001b[39;49;00m\u001b[37m\u001b[39;49;00m"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取SQL中可以使用的表名\n",
    "ibis.to_sql(demo_t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "498befe1-f1ca-4731-86a2-2e13fff9ac9e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace\">┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃<span style=\"font-weight: bold\"> 类别   </span>┃<span style=\"font-weight: bold\"> 组内样… </span>┃<span style=\"font-weight: bold\"> 组内平均… </span>┃<span style=\"font-weight: bold\"> 组内中… </span>┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">string</span> │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">int64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span>   │ <span style=\"color: #7f7f7f; text-decoration-color: #7f7f7f\">float64</span> │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">a     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668194</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.980634</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.977</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">b     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1667820</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.983055</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.994</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">c     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665020</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.030410</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.036</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">d     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1664831</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.021382</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.013</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">e     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1665392</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.985976</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">49.965</span> │\n",
       "│ <span style=\"color: #008000; text-decoration-color: #008000\">f     </span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">1668743</span> │ <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.014333</span> │  <span style=\"color: #008080; text-decoration-color: #008080; font-weight: bold\">50.018</span> │\n",
       "└────────┴─────────┴───────────┴─────────┘\n",
       "</pre>\n"
      ],
      "text/plain": [
       "┏━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━┓\n",
       "┃\u001b[1m \u001b[0m\u001b[1m类别\u001b[0m\u001b[1m  \u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内样…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内平均…\u001b[0m\u001b[1m \u001b[0m┃\u001b[1m \u001b[0m\u001b[1m组内中…\u001b[0m\u001b[1m \u001b[0m┃\n",
       "┡━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━┩\n",
       "│ \u001b[2mstring\u001b[0m │ \u001b[2mint64\u001b[0m   │ \u001b[2mfloat64\u001b[0m   │ \u001b[2mfloat64\u001b[0m │\n",
       "├────────┼─────────┼───────────┼─────────┤\n",
       "│ \u001b[32ma     \u001b[0m │ \u001b[1;36m1668194\u001b[0m │ \u001b[1;36m49.980634\u001b[0m │  \u001b[1;36m49.977\u001b[0m │\n",
       "│ \u001b[32mb     \u001b[0m │ \u001b[1;36m1667820\u001b[0m │ \u001b[1;36m49.983055\u001b[0m │  \u001b[1;36m49.994\u001b[0m │\n",
       "│ \u001b[32mc     \u001b[0m │ \u001b[1;36m1665020\u001b[0m │ \u001b[1;36m50.030410\u001b[0m │  \u001b[1;36m50.036\u001b[0m │\n",
       "│ \u001b[32md     \u001b[0m │ \u001b[1;36m1664831\u001b[0m │ \u001b[1;36m50.021382\u001b[0m │  \u001b[1;36m50.013\u001b[0m │\n",
       "│ \u001b[32me     \u001b[0m │ \u001b[1;36m1665392\u001b[0m │ \u001b[1;36m49.985976\u001b[0m │  \u001b[1;36m49.965\u001b[0m │\n",
       "│ \u001b[32mf     \u001b[0m │ \u001b[1;36m1668743\u001b[0m │ \u001b[1;36m50.014333\u001b[0m │  \u001b[1;36m50.018\u001b[0m │\n",
       "└────────┴─────────┴───────────┴─────────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 注意下方表名需与上方打印结果一致\n",
    "demo_t.sql('''\n",
    "SELECT\n",
    "  *\n",
    "FROM (\n",
    "  SELECT\n",
    "    \"t0\".\"类别\",\n",
    "    COUNT(\"t0\".\"数值\") AS \"组内样本数\",\n",
    "    AVG(\"t0\".\"数值\") AS \"组内平均数\",\n",
    "    MEDIAN(\"t0\".\"数值\") AS \"组内中位数\"\n",
    "  FROM \"ibis_read_parquet_qvyjomcxznf5hfsdjtgvenbsiq\" AS \"t0\"\n",
    "  GROUP BY\n",
    "    1\n",
    ") AS \"t1\"\n",
    "ORDER BY\n",
    "  \"t1\".\"类别\" ASC\n",
    "''')"
   ]
  }
 ],
 "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.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}