{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datebase Statistics Example\n",
    "This notebooks shows a couple of basic examples about how to create statistics from details stored in your Home Assistant database. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup Database connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Instead of SQLAlchemy is the built-in Python support for SQLite used.\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Your database URL\n",
    "DB_URL = \"/home/PATH/.homeassistant/home-assistant_v2.db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(DB_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Details about your entities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get all entities and their occurrence from your database. Limit to 10."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "entities = conn.execute(\"SELECT entity_id, COUNT(*) as count FROM states \"\n",
    "                        \"GROUP BY entity_id ORDER BY count DESC LIMIT 10\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('sensor.max_sensor', 7978),\n",
       " ('binary_sensor.random_trend', 5086),\n",
       " ('sensor.random_sensor', 5052),\n",
       " ('sensor.random_sensor_2', 3160),\n",
       " ('binary_sensor.random_binary_sensor', 703),\n",
       " ('sensor.netdata_load', 658),\n",
       " ('binary_sensor.iss', 530),\n",
       " ('sensor.b4d1_uptime', 528),\n",
       " ('sensor.b4d1_signal', 361),\n",
       " ('sun.sun', 171)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entities.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Details about one entities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most SQL dialects support mathematical functions, especially for statistical analysis. For SQLite those are called [Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "stats = conn.execute(\"SELECT entity_id, COUNT(state) as count, MAX(state) as max, \"\n",
    "                     \"MIN(state) as min, AVG(state) as mean FROM states \"\n",
    "                     \"WHERE entity_id = 'sensor.random_sensor'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Entitiy ID :  sensor.random_sensor\n",
      "count      :  5052\n",
      "max        :  9\n",
      "min        :  0\n",
      "mean       :  9.877672209026128\n"
     ]
    }
   ],
   "source": [
    "for row in stats:\n",
    "   print(\"Entitiy ID : \", row[0])\n",
    "   print(\"count      : \", row[1])\n",
    "   print(\"max        : \", row[2])\n",
    "   print(\"min        : \", row[3])\n",
    "   print(\"mean       : \", row[4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count the occurrence of a single values. Could be useful if you want to adjust your climate units."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stats1 = conn.execute(\"SELECT entity_id, COUNT(state) FROM states \"\n",
    "                      \"WHERE entity_id = 'sensor.random_sensor' AND state = 6\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('sensor.random_sensor', 234)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats1.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Show only values which exceeded a threshold."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stats2 = conn.execute(\"SELECT entity_id, COUNT(state) FROM states \"\n",
    "                      \"WHERE entity_id = 'sensor.random_sensor' AND state > 5\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('sensor.random_sensor', 921)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats2.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine the states of a binary sensor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stats3 = conn.execute(\"SELECT state, COUNT(state) FROM states \"\n",
    "                      \"WHERE entity_id = 'binary_sensor.movement' \"\n",
    "                      \"AND state = 'off' OR state = 'on' GROUP BY state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('on', 2883)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats3.fetchall()"
   ]
  }
 ],
 "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}