commerce/exercise-3.4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Related Products Recommendation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "확장 기능 로드"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql\n",
    "%run libdb.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "데이터베이스 접속"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@postgres'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# postgresql+psycopg2://사용자ID:password@127.0.0.1:5432/데이터베이스\n",
    "%sql postgresql+psycopg2://postgres:@127.0.0.1:5432/postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Related Products based on Co-occurrence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "600051 rows affected.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop index if exists idx_view_coo_rel;\n",
    "create index idx_view_coo_rel on view_log (uid, session_id, item_id);\n",
    "\n",
    "drop index if exists idx_view_coo_rel2;\n",
    "create index idx_view_coo_rel2 on view_log (uid, session_id);\n",
    "\n",
    "drop table if exists coo_rel;\n",
    "create table coo_rel as\n",
    "select\n",
    "    *\n",
    "from (\n",
    "    select\n",
    "        target_item_id,\n",
    "        cross_sell_item_id,\n",
    "        score,\n",
    "        row_number() over (partition by target_item_id order by score desc) rank\n",
    "    from (\n",
    "        select\n",
    "            a.item_id target_item_id,\n",
    "            b.item_id cross_sell_item_id,\n",
    "            count(*) score\n",
    "        from view_log a\n",
    "            inner join view_log b on \n",
    "                a.uid = b.uid and \n",
    "                a.session_id = b.session_id and\n",
    "                a.item_id != b.item_id\n",
    "        group by a.item_id, b.item_id\n",
    "    ) a\n",
    ") a\n",
    "where rank <= 20\n",
    ";\n",
    "\n",
    "drop index if exists idx_coo_rel;\n",
    "create index idx_coo_rel on coo_rel(target_item_id, rank);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>target_item_id</th>\n",
       "        <th>cross_sell_item_id</th>\n",
       "        <th>score</th>\n",
       "        <th>rank</th>\n",
       "        <th>item_id</th>\n",
       "        <th>item_image</th>\n",
       "        <th>price</th>\n",
       "        <th>category1</th>\n",
       "        <th>category2</th>\n",
       "        <th>category3</th>\n",
       "        <th>category4</th>\n",
       "        <th>brand</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>141dfb4</td>\n",
       "        <td>893</td>\n",
       "        <td>1</td>\n",
       "        <td>141dfb4</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/446/12314446_h.jpg</td>\n",
       "        <td>29610</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>7092324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>3d4826b</td>\n",
       "        <td>522</td>\n",
       "        <td>2</td>\n",
       "        <td>3d4826b</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/620/12042620_h.jpg</td>\n",
       "        <td>31410</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>adf17cd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>99038bb</td>\n",
       "        <td>449</td>\n",
       "        <td>3</td>\n",
       "        <td>99038bb</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/445/12314445_h.jpg</td>\n",
       "        <td>31410</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>7092324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>22afeb9</td>\n",
       "        <td>124</td>\n",
       "        <td>4</td>\n",
       "        <td>22afeb9</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/825/12391825_h.jpg</td>\n",
       "        <td>32310</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>7d1f1f0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>3a608ba</td>\n",
       "        <td>110</td>\n",
       "        <td>5</td>\n",
       "        <td>3a608ba</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/087/12077087_h.jpg</td>\n",
       "        <td>32310</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>adf17cd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>46f51f1</td>\n",
       "        <td>49</td>\n",
       "        <td>6</td>\n",
       "        <td>46f51f1</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/571/12235571_h.jpg</td>\n",
       "        <td>27000</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>d5902d6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>04c6c53</td>\n",
       "        <td>48</td>\n",
       "        <td>7</td>\n",
       "        <td>04c6c53</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/539/12249539_h.jpg</td>\n",
       "        <td>35910</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>aa23ef2</td>\n",
       "        <td>4f61228</td>\n",
       "        <td>a3c5359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>998e590</td>\n",
       "        <td>46</td>\n",
       "        <td>8</td>\n",
       "        <td>998e590</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/792/11332792_h.jpg</td>\n",
       "        <td>28620</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>adf17cd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>7bccef8</td>\n",
       "        <td>45</td>\n",
       "        <td>9</td>\n",
       "        <td>7bccef8</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/741/12370741_h.jpg</td>\n",
       "        <td>160100</td>\n",
       "        <td>de19bda</td>\n",
       "        <td>ace4b8b</td>\n",
       "        <td>4674a6e</td>\n",
       "        <td>a410d49</td>\n",
       "        <td>e90b4e9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ac616a3</td>\n",
       "        <td>d31c83d</td>\n",
       "        <td>44</td>\n",
       "        <td>10</td>\n",
       "        <td>d31c83d</td>\n",
       "        <td>http://fc-img.recobell.io/images/goods/440/12314440_h.jpg</td>\n",
       "        <td>13410</td>\n",
       "        <td>a3cf17c</td>\n",
       "        <td>4c2d9b5</td>\n",
       "        <td>726fe95</td>\n",
       "        <td>79bcf0d</td>\n",
       "        <td>7092324</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ac616a3', '141dfb4', 893, 1, '141dfb4', 'http://fc-img.recobell.io/images/goods/446/12314446_h.jpg', 29610, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', '7092324'),\n",
       " ('ac616a3', '3d4826b', 522, 2, '3d4826b', 'http://fc-img.recobell.io/images/goods/620/12042620_h.jpg', 31410, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', 'adf17cd'),\n",
       " ('ac616a3', '99038bb', 449, 3, '99038bb', 'http://fc-img.recobell.io/images/goods/445/12314445_h.jpg', 31410, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', '7092324'),\n",
       " ('ac616a3', '22afeb9', 124, 4, '22afeb9', 'http://fc-img.recobell.io/images/goods/825/12391825_h.jpg', 32310, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', '7d1f1f0'),\n",
       " ('ac616a3', '3a608ba', 110, 5, '3a608ba', 'http://fc-img.recobell.io/images/goods/087/12077087_h.jpg', 32310, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', 'adf17cd'),\n",
       " ('ac616a3', '46f51f1', 49, 6, '46f51f1', 'http://fc-img.recobell.io/images/goods/571/12235571_h.jpg', 27000, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', 'd5902d6'),\n",
       " ('ac616a3', '04c6c53', 48, 7, '04c6c53', 'http://fc-img.recobell.io/images/goods/539/12249539_h.jpg', 35910, 'a3cf17c', '4c2d9b5', 'aa23ef2', '4f61228', 'a3c5359'),\n",
       " ('ac616a3', '998e590', 46, 8, '998e590', 'http://fc-img.recobell.io/images/goods/792/11332792_h.jpg', 28620, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', 'adf17cd'),\n",
       " ('ac616a3', '7bccef8', 45, 9, '7bccef8', 'http://fc-img.recobell.io/images/goods/741/12370741_h.jpg', 160100, 'de19bda', 'ace4b8b', '4674a6e', 'a410d49', 'e90b4e9'),\n",
       " ('ac616a3', 'd31c83d', 44, 10, 'd31c83d', 'http://fc-img.recobell.io/images/goods/440/12314440_h.jpg', 13410, 'a3cf17c', '4c2d9b5', '726fe95', '79bcf0d', '7092324')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select *\n",
    "from coo_rel a\n",
    "inner join product b on a.cross_sell_item_id = b.item_id\n",
    "where target_item_id = 'ac616a3'\n",
    "order by rank limit 10;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/442/12314442_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/446/12314446_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/620/12042620_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/445/12314445_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/825/12391825_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/087/12077087_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/571/12235571_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/539/12249539_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/792/11332792_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/741/12370741_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/440/12314440_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/437/12314437_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/623/12408623_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/985/12357985_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/438/12314438_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/905/11492905_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/594/12397594_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/644/12284644_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/365/12277365_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/221/12412221_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/641/12389641_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "item_id = 'ac616a3'\n",
    "result = %sql select * from product where item_id = :item_id;\n",
    "displayImageUrls(getImageUrls(result, 'item_image'))\n",
    "result2 = %sql select * from coo_rel a inner join product b on a.cross_sell_item_id = b.item_id where target_item_id = :item_id order by rank limit 20;\n",
    "displayImageUrls(getImageUrls(result2, 'item_image'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Related Products based on TF-IDF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "51481 rows affected.\n",
      "Done.\n",
      "51481 rows affected.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop index if exists idx_tfidf_1;\n",
    "drop index if exists idx_tfidf_2;\n",
    "\n",
    "create index idx_tfidf_1 on view_log (session_id);\n",
    "create index idx_tfidf_2 on view_log (item_id, session_id);\n",
    "\n",
    "select count(distinct session_id) \n",
    "from view_log; -- N\n",
    "\n",
    "select item_id, count(session_id) df \n",
    "from view_log \n",
    "group by item_id; -- df\n",
    "\n",
    "drop table if exists item_idf;\n",
    "\n",
    "create table item_idf as\n",
    "select item_id, \n",
    "    log((select count(distinct session_id) from view_log)/\n",
    "        (count(distinct session_id) + 1)) idf\n",
    "from view_log a\n",
    "group by item_id;\n",
    "\n",
    "create index idx_idf on item_idf(item_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TF-IDf by session(document)-item(word)\n",
    "일반적으로 사용자 단위 보다, Session 단위로 처리시 성능이 좋음"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "544732 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists tfidf;\n",
    "\n",
    "create table tfidf as\n",
    "select\n",
    "    a.session_id,\n",
    "    a.item_id,\n",
    "    a.cnt*b.idf tfidf\n",
    "from (\n",
    "    select session_id, item_id, count(*) cnt \n",
    "    from view_log \n",
    "    group by session_id, item_id\n",
    "    ) a\n",
    "    inner join item_idf b on a.item_id = b.item_id;\n",
    "\n",
    "drop index if exists idx_tfidf;\n",
    "create index idx_tfidf on tfidf(session_id, item_id);\n",
    "\n",
    "drop index if exists idx_tfidf2;\n",
    "create index idx_tfidf2 on tfidf(session_id);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Related Products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "600051 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists tfidf_rel;\n",
    "\n",
    "create table tfidf_rel as\n",
    "select\n",
    "    *\n",
    "from (\n",
    "    select\n",
    "        target_item_id,\n",
    "        cross_sell_item_id,\n",
    "        score,\n",
    "        row_number() over (partition by target_item_id order by score desc) rank\n",
    "    from (\n",
    "        select\n",
    "            a.item_id target_item_id,\n",
    "            b.item_id cross_sell_item_id,\n",
    "            sum(a.tfidf*b.tfidf) score\n",
    "        from tfidf a\n",
    "        inner join tfidf b on a.session_id = b.session_id\n",
    "        where a.item_id != b.item_id\n",
    "        group by a.item_id, b.item_id\n",
    "    ) a\n",
    ") a\n",
    "where rank <= 20\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/442/12314442_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/446/12314446_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/445/12314445_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/620/12042620_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/825/12391825_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/087/12077087_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/571/12235571_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/440/12314440_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/437/12314437_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/792/11332792_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/438/12314438_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/539/12249539_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/518/12221518_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/841/12113841_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/905/11492905_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/225/12110225_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/365/12277365_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/644/12284644_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/623/12408623_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/966/12169966_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/291/12226291_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "item_id = 'ac616a3'\n",
    "result = %sql select * from product where item_id = :item_id;\n",
    "displayImageUrls(getImageUrls(result, 'item_image'))\n",
    "result2 = %sql select * from tfidf_rel a inner join product b on a.cross_sell_item_id = b.item_id where target_item_id = :item_id order by rank limit 20;\n",
    "displayImageUrls(getImageUrls(result2, 'item_image'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Related Products based on TF-IDF and Cosine Similarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "544732 rows affected.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists coo_cs;\n",
    "\n",
    "create table coo_cs as\n",
    "select \n",
    "    item_id,\n",
    "    session_id,\n",
    "    count(distinct session_id) / (sum(count(distinct session_id)) over (partition by item_id)) cs\n",
    "from view_log\n",
    "group by item_id, session_id;\n",
    "\n",
    "drop index if exists idx_coo_cs;\n",
    "\n",
    "create index idx_coo_cs on coo_cs(session_id, item_id, cs);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Related Products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "1958945 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists coo_cs_rel;\n",
    "\n",
    "create table coo_cs_rel as\n",
    "select\n",
    "    *\n",
    "from (\n",
    "    select\n",
    "        target_item_id,\n",
    "        cross_sell_item_id,\n",
    "        score,\n",
    "        row_number() over (partition by target_item_id order by score desc) rank\n",
    "    from (\n",
    "        select\n",
    "            a.item_id target_item_id,\n",
    "            b.item_id cross_sell_item_id,\n",
    "            max(a.cs) * max(b.cs) score\n",
    "        from coo_cs a\n",
    "            inner join coo_cs b on a.session_id = b.session_id\n",
    "        group by a.item_id, b.item_id\n",
    "    ) a\n",
    ") a\n",
    "where rank <= 20\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/442/12314442_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<img src='http://fc-img.recobell.io/images/goods/773/12367773_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/109/12347109_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/572/12324572_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/551/12149551_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/084/12368084_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/053/12393053_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/537/10694537_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/193/10138193_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/588/12347588_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/527/11816527_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/704/11444704_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/282/12350282_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/954/11107954_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/573/11692573_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/624/10367624_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/090/11365090_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/014/11641014_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/324/12029324_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/314/12400314_h.jpg' width='170' style='display:inline-block'><img src='http://fc-img.recobell.io/images/goods/841/10156841_h.jpg' width='170' style='display:inline-block'>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "item_id = 'ac616a3'\n",
    "result = %sql select * from product where item_id = :item_id;\n",
    "displayImageUrls(getImageUrls(result, 'item_image'))\n",
    "result2 = %sql select * from coo_cs_rel a inner join product b on a.cross_sell_item_id = b.item_id where target_item_id = :item_id order by rank limit 20;\n",
    "displayImageUrls(getImageUrls(result2, 'item_image'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}