{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "09967fe5", "metadata": {}, "outputs": [ { "ename": "ImportError", "evalue": "no pq wrapper available.\nAttempts made:\n- couldn't import psycopg 'c' implementation: No module named 'psycopg_c'\n- couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'\n- couldn't import psycopg 'python' implementation: libpq library not found", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mImportError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[1]\u001b[39m\u001b[32m, line 1\u001b[39m\n\u001b[32m----> \u001b[39m\u001b[32m1\u001b[39m get_ipython().run_line_magic(\u001b[33m'load_ext'\u001b[39m, \u001b[33m'sql'\u001b[39m)\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\importlib\\__init__.py:88\u001b[39m, in \u001b[36mimport_module\u001b[39m\u001b[34m(name, package)\u001b[39m\n\u001b[32m 86\u001b[39m \u001b[38;5;28;01mbreak\u001b[39;00m\n\u001b[32m 87\u001b[39m level += \u001b[32m1\u001b[39m\n\u001b[32m---> \u001b[39m\u001b[32m88\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[30;43m_bootstrap\u001b[39;49m\u001b[30;43m.\u001b[39;49m\u001b[30;43m_gcd_import\u001b[39;49m\u001b[30;43m(\u001b[39;49m\u001b[30;43mname\u001b[39;49m\u001b[30;43m[\u001b[39;49m\u001b[30;43mlevel\u001b[39;49m\u001b[30;43m:\u001b[39;49m\u001b[30;43m]\u001b[39;49m\u001b[30;43m,\u001b[39;49m\u001b[30;43m \u001b[39;49m\u001b[30;43mpackage\u001b[39;49m\u001b[30;43m,\u001b[39;49m\u001b[30;43m \u001b[39;49m\u001b[30;43mlevel\u001b[39;49m\u001b[30;43m)\u001b[39;49m\n", "\u001b[36mFile \u001b[39m\u001b[32m:1406\u001b[39m, in \u001b[36m_gcd_import\u001b[39m\u001b[34m(name, package, level)\u001b[39m\n\u001b[32m 1402\u001b[39m \"\"\"\n\u001b[32m 1403\u001b[39m _sanity_check(name, package, level)\n\u001b[32m 1404\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m level > \u001b[32m0\u001b[39m:\n\u001b[32m 1405\u001b[39m name = _resolve_name(name, package, level)\n\u001b[32m-> \u001b[39m\u001b[32m1406\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m _find_and_load(name, _gcd_import)\n", "\u001b[36mFile \u001b[39m\u001b[32m:1371\u001b[39m, in \u001b[36m_find_and_load\u001b[39m\u001b[34m(name, import_)\u001b[39m\n\u001b[32m 1367\u001b[39m getattr(getattr(module, \u001b[33m\"__spec__\"\u001b[39m, \u001b[38;5;28;01mNone\u001b[39;00m), \u001b[33m\"_initializing\"\u001b[39m, \u001b[38;5;28;01mFalse\u001b[39;00m)):\n\u001b[32m 1368\u001b[39m \u001b[38;5;28;01mwith\u001b[39;00m _ModuleLockManager(name):\n\u001b[32m 1369\u001b[39m module = sys.modules.get(name, _NEEDS_LOADING)\n\u001b[32m 1370\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m module \u001b[38;5;28;01mis\u001b[39;00m _NEEDS_LOADING:\n\u001b[32m-> \u001b[39m\u001b[32m1371\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m _find_and_load_unlocked(name, import_)\n\u001b[32m 1372\u001b[39m \n\u001b[32m 1373\u001b[39m \u001b[38;5;66;03m# Optimization: only call _bootstrap._lock_unlock_module() if\u001b[39;00m\n\u001b[32m 1374\u001b[39m \u001b[38;5;66;03m# module.__spec__._initializing is True.\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32m:1344\u001b[39m, in \u001b[36m_find_and_load_unlocked\u001b[39m\u001b[34m(name, import_)\u001b[39m\n\u001b[32m 1340\u001b[39m parent_spec._uninitialized_submodules.append(child)\n\u001b[32m 1341\u001b[39m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[32m 1342\u001b[39m module = _load_unlocked(spec)\n\u001b[32m 1343\u001b[39m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[32m-> \u001b[39m\u001b[32m1344\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m parent_spec:\n\u001b[32m 1345\u001b[39m parent_spec._uninitialized_submodules.pop()\n\u001b[32m 1346\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m parent:\n\u001b[32m 1347\u001b[39m \u001b[38;5;66;03m# Set the module as an attribute on its parent.\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32m:953\u001b[39m, in \u001b[36m_load_unlocked\u001b[39m\u001b[34m(spec)\u001b[39m\n\u001b[32m 949\u001b[39m module = sys.modules.pop(spec.name)\n\u001b[32m 950\u001b[39m sys.modules[spec.name] = module\n\u001b[32m 951\u001b[39m _verbose_message(\u001b[33m'import {!r} # {!r}'\u001b[39m, spec.name, spec.loader)\n\u001b[32m 952\u001b[39m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[32m--> \u001b[39m\u001b[32m953\u001b[39m spec._initializing = \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[32m 954\u001b[39m \n\u001b[32m 955\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m module\n", "\u001b[36mFile \u001b[39m\u001b[32m:759\u001b[39m, in \u001b[36m_LoaderBasics.exec_module\u001b[39m\u001b[34m(self, module)\u001b[39m\n\u001b[32m 755\u001b[39m code = self.get_code(module.__name__)\n\u001b[32m 756\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m code \u001b[38;5;28;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[32m 757\u001b[39m raise ImportError(f'cannot load module {module.__name__!r} when '\n\u001b[32m 758\u001b[39m \u001b[33m'get_code() returns None'\u001b[39m)\n\u001b[32m--> \u001b[39m\u001b[32m759\u001b[39m _bootstrap._call_with_frames_removed(exec, code, module.__dict__)\n", "\u001b[36mFile \u001b[39m\u001b[32m:491\u001b[39m, in \u001b[36m_call_with_frames_removed\u001b[39m\u001b[34m(f, *args, **kwds)\u001b[39m\n\u001b[32m 487\u001b[39m Use it instead of a normal call \u001b[38;5;28;01min\u001b[39;00m places where including the importlib\n\u001b[32m 488\u001b[39m frames introduces unwanted noise into the traceback (e.g. when executing\n\u001b[32m 489\u001b[39m module code)\n\u001b[32m 490\u001b[39m \"\"\"\n\u001b[32m--> \u001b[39m\u001b[32m491\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m f(*args, **kwds)\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\sql\\__init__.py:1\u001b[39m\n\u001b[32m----> \u001b[39m\u001b[32m1\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mmagic\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m load_ipython_extension\n\u001b[32m 4\u001b[39m __version__ = \u001b[33m\"\u001b[39m\u001b[33m0.11.1\u001b[39m\u001b[33m\"\u001b[39m\n\u001b[32m 7\u001b[39m __all__ = [\u001b[33m\"\u001b[39m\u001b[33mload_ipython_extension\u001b[39m\u001b[33m\"\u001b[39m]\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\sql\\magic.py:35\u001b[39m\n\u001b[32m 33\u001b[39m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mconnection\u001b[39;00m\n\u001b[32m 34\u001b[39m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mparse\u001b[39;00m\n\u001b[32m---> \u001b[39m\u001b[32m35\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mrun\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mrun\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m run_statements\n\u001b[32m 36\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mparse\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m _option_strings_from_parser\n\u001b[32m 37\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m display, exceptions\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\sql\\run\\run.py:5\u001b[39m\n\u001b[32m 3\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m exceptions, display\n\u001b[32m 4\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mrun\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mresultset\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m ResultSet\n\u001b[32m----> \u001b[39m\u001b[32m5\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msql\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mrun\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mpgspecial\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m handle_postgres_special\n\u001b[32m 8\u001b[39m \u001b[38;5;66;03m# TODO: conn also has access to config, we should clean this up to provide a clean\u001b[39;00m\n\u001b[32m 9\u001b[39m \u001b[38;5;66;03m# way to access the config\u001b[39;00m\n\u001b[32m 10\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34mrun_statements\u001b[39m(conn, sql, config, parameters=\u001b[38;5;28;01mNone\u001b[39;00m):\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\sql\\run\\pgspecial.py:2\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[32m----> \u001b[39m\u001b[32m2\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01mpgspecial\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mmain\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m PGSpecial\n\u001b[32m 3\u001b[39m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mModuleNotFoundError\u001b[39;00m:\n\u001b[32m 4\u001b[39m PGSpecial = \u001b[38;5;28;01mNone\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\pgspecial\\__init__.py:14\u001b[39m\n\u001b[32m 10\u001b[39m __all__.append(defn.\u001b[34m__name__\u001b[39m)\n\u001b[32m 11\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m defn\n\u001b[32m---> \u001b[39m\u001b[32m14\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01m.\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m dbcommands, iocommands \u001b[38;5;66;03m# noqa\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\pgspecial\\dbcommands.py:7\u001b[39m\n\u001b[32m 4\u001b[39m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01msubprocess\u001b[39;00m\n\u001b[32m 5\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01mcollections\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m namedtuple\n\u001b[32m----> \u001b[39m\u001b[32m7\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01mpsycopg\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01msql\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m SQL\n\u001b[32m 9\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01m.\u001b[39;00m\u001b[34;01mmain\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m special_command\n\u001b[32m 11\u001b[39m TableInfo = namedtuple(\n\u001b[32m 12\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mTableInfo\u001b[39m\u001b[33m\"\u001b[39m,\n\u001b[32m 13\u001b[39m [\n\u001b[32m (...)\u001b[39m\u001b[32m 25\u001b[39m ],\n\u001b[32m 26\u001b[39m )\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\psycopg\\__init__.py:9\u001b[39m\n\u001b[32m 5\u001b[39m \u001b[38;5;66;03m# Copyright (C) 2020 The Psycopg Team\u001b[39;00m\n\u001b[32m 7\u001b[39m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01mlogging\u001b[39;00m\n\u001b[32m----> \u001b[39m\u001b[32m9\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01m.\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m pq \u001b[38;5;66;03m# noqa: F401 import early to stabilize side effects\u001b[39;00m\n\u001b[32m 10\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01m.\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m dbapi20, postgres, types\n\u001b[32m 11\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01m.\u001b[39;00m\u001b[34;01m_tpc\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m Xid\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\psycopg\\pq\\__init__.py:114\u001b[39m\n\u001b[32m 107\u001b[39m sattempts = \u001b[33m\"\u001b[39m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[33m\"\u001b[39m.join(\u001b[33mf\u001b[39m\u001b[33m\"\u001b[39m\u001b[33m- \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mattempt\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\u001b[39m \u001b[38;5;28;01mfor\u001b[39;00m attempt \u001b[38;5;129;01min\u001b[39;00m attempts)\n\u001b[32m 108\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mImportError\u001b[39;00m(\u001b[33mf\u001b[39m\u001b[33m\"\"\"\u001b[39m\u001b[38;5;130;01m\\\u001b[39;00m\n\u001b[32m 109\u001b[39m \u001b[33mno pq wrapper available.\u001b[39m\n\u001b[32m 110\u001b[39m \u001b[33mAttempts made:\u001b[39m\n\u001b[32m 111\u001b[39m \u001b[38;5;132;01m{\u001b[39;00msattempts\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\"\"\u001b[39m)\n\u001b[32m--> \u001b[39m\u001b[32m114\u001b[39m \u001b[30;43mimport_from_libpq\u001b[39;49m\u001b[30;43m(\u001b[39;49m\u001b[30;43m)\u001b[39;49m\n\u001b[32m 116\u001b[39m __all__ = (\n\u001b[32m 117\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mConnStatus\u001b[39m\u001b[33m\"\u001b[39m,\n\u001b[32m 118\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mPipelineStatus\u001b[39m\u001b[33m\"\u001b[39m,\n\u001b[32m (...)\u001b[39m\u001b[32m 133\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mversion_pretty\u001b[39m\u001b[33m\"\u001b[39m,\n\u001b[32m 134\u001b[39m )\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\user\\miniconda3\\Lib\\site-packages\\psycopg\\pq\\__init__.py:108\u001b[39m, in \u001b[36mimport_from_libpq\u001b[39m\u001b[34m()\u001b[39m\n\u001b[32m 106\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[32m 107\u001b[39m sattempts = \u001b[33m\"\u001b[39m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[33m\"\u001b[39m.join(\u001b[33mf\u001b[39m\u001b[33m\"\u001b[39m\u001b[33m- \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mattempt\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\u001b[39m \u001b[38;5;28;01mfor\u001b[39;00m attempt \u001b[38;5;129;01min\u001b[39;00m attempts)\n\u001b[32m--> \u001b[39m\u001b[32m108\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mImportError\u001b[39;00m(\u001b[33mf\u001b[39m\u001b[33m\"\"\"\u001b[39m\u001b[38;5;130;01m\\\u001b[39;00m\n\u001b[32m 109\u001b[39m \u001b[33mno pq wrapper available.\u001b[39m\n\u001b[32m 110\u001b[39m \u001b[33mAttempts made:\u001b[39m\n\u001b[32m 111\u001b[39m \u001b[38;5;132;01m{\u001b[39;00msattempts\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\"\"\u001b[39m)\n", "\u001b[31mImportError\u001b[39m: no pq wrapper available.\nAttempts made:\n- couldn't import psycopg 'c' implementation: No module named 'psycopg_c'\n- couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'\n- couldn't import psycopg 'python' implementation: libpq library not found" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": null, "id": "f98765a3", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "'pip' is not recognized as an internal or external command,\n", "operable program or batch file.\n", "'pip' is not recognized as an internal or external command,\n", "operable program or batch file.\n", "'pip' is not recognized as an internal or external command,\n", "operable program or batch file.\n" ] } ], "source": [ "!pip install jupysql duckdb-engine\n", "!pip install sqlalchemy\n", "from sqlalchemy import create_engine\n", "!pip install psycopg2" ] }, { "cell_type": "code", "execution_count": null, "id": "2a3171d5", "metadata": {}, "outputs": [], "source": [ ",," ] }, { "cell_type": "code", "execution_count": null, "id": "1ff0842f", "metadata": {}, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "%config SqlMagic.feedback = False\n", "%config SqlMagic.displaycon = False" ] }, { "cell_type": "code", "execution_count": null, "id": "aaeb3a49", "metadata": {}, "outputs": [], "source": [ "HOST = \"\"\n", "PORT = \"\"\n", "USER = \"\"\n", "PASSWORD = \"\"\n", "DATABASE = \"energy_consump\"\n", "\n", "engine = create_engine(\n", " f\"postgresql+psycopg2://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?sslmode=require\"\n", ")\n", "%sql postgresql+psycopg2://:@:/energy_consump?sslmode=require" ] }, { "cell_type": "code", "execution_count": null, "id": "8b1ea3c4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'postgresql+psycopg2://:***@:/energy_consump?sslmode=require'" ], "text/plain": [ "Running query in 'postgresql+psycopg2://:***@:/energy_consump?sslmode=require'" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stderr", "output_type": "stream", "text": [ "RuntimeError: (psycopg2.errors.DuplicateDatabase) database \"energy_consump\" already exists\n", "\n", "[SQL: create database energy_consump]\n", "(Background on this error at: https://sqlalche.me/e/20/f405)\n" ] } ], "source": [ "%%sql\n", "create database energy_consump" ] }, { "cell_type": "code", "execution_count": null, "id": "7846d045", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'postgresql+psycopg2://:***@:/energy_consump?sslmode=require'" ], "text/plain": [ "Running query in 'postgresql+psycopg2://:***@:/energy_consump?sslmode=require'" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stderr", "output_type": "stream", "text": [ "RuntimeError: (psycopg2.errors.DuplicateSchema) schema \"energy\" already exists\n", "\n", "[SQL: create schema energy]\n", "(Background on this error at: https://sqlalche.me/e/20/f405)\n" ] } ], "source": [ "%%sql\n", "create schema energy" ] }, { "cell_type": "code", "execution_count": null, "id": "79be1ca4", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['Energy Consumptions', 'Rates', 'Building Master']" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "file= pd.ExcelFile(r\"data/Energy Consumptions Dataset.xlsx\")\n", "file.sheet_names" ] }, { "cell_type": "code", "execution_count": null, "id": "a6bcda12", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DateBuildingWater ConsumptionElectricity ConsumptionGas Consumption
02016-01-01B1000346159388193378
12016-01-01B1001281717462522187
22016-01-01B1002259530410913173
32016-01-01B1003309176255773581
42016-01-01B1004257742339174709
..................
5232019-12-01B1006296157339384931
5242019-12-01B1007319388371064748
5252019-12-01B1008251866470795909
5262019-12-01B1009280510481345492
5272019-12-01B1010443282420275610
\n", "

528 rows × 5 columns

\n", "
" ], "text/plain": [ " Date Building Water Consumption Electricity Consumption \\\n", "0 2016-01-01 B1000 346159 38819 \n", "1 2016-01-01 B1001 281717 46252 \n", "2 2016-01-01 B1002 259530 41091 \n", "3 2016-01-01 B1003 309176 25577 \n", "4 2016-01-01 B1004 257742 33917 \n", ".. ... ... ... ... \n", "523 2019-12-01 B1006 296157 33938 \n", "524 2019-12-01 B1007 319388 37106 \n", "525 2019-12-01 B1008 251866 47079 \n", "526 2019-12-01 B1009 280510 48134 \n", "527 2019-12-01 B1010 443282 42027 \n", "\n", " Gas Consumption \n", "0 3378 \n", "1 2187 \n", "2 3173 \n", "3 3581 \n", "4 4709 \n", ".. ... \n", "523 4931 \n", "524 4748 \n", "525 5909 \n", "526 5492 \n", "527 5610 \n", "\n", "[528 rows x 5 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "consumption=pd.read_excel(r\"data/Energy Consumptions Dataset.xlsx\",sheet_name=\"Energy Consumptions\")\n", "consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "1b1c5204", "metadata": {}, "outputs": [], "source": [ "consumption.columns = consumption.columns.str.lower()" ] }, { "cell_type": "code", "execution_count": null, "id": "76c9136c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datebuildingwater consumptionelectricity consumptiongas consumption
02016-01-01B1000346159388193378
12016-01-01B1001281717462522187
22016-01-01B1002259530410913173
32016-01-01B1003309176255773581
42016-01-01B1004257742339174709
..................
5232019-12-01B1006296157339384931
5242019-12-01B1007319388371064748
5252019-12-01B1008251866470795909
5262019-12-01B1009280510481345492
5272019-12-01B1010443282420275610
\n", "

528 rows × 5 columns

\n", "
" ], "text/plain": [ " date building water consumption electricity consumption \\\n", "0 2016-01-01 B1000 346159 38819 \n", "1 2016-01-01 B1001 281717 46252 \n", "2 2016-01-01 B1002 259530 41091 \n", "3 2016-01-01 B1003 309176 25577 \n", "4 2016-01-01 B1004 257742 33917 \n", ".. ... ... ... ... \n", "523 2019-12-01 B1006 296157 33938 \n", "524 2019-12-01 B1007 319388 37106 \n", "525 2019-12-01 B1008 251866 47079 \n", "526 2019-12-01 B1009 280510 48134 \n", "527 2019-12-01 B1010 443282 42027 \n", "\n", " gas consumption \n", "0 3378 \n", "1 2187 \n", "2 3173 \n", "3 3581 \n", "4 4709 \n", ".. ... \n", "523 4931 \n", "524 4748 \n", "525 5909 \n", "526 5492 \n", "527 5610 \n", "\n", "[528 rows x 5 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "614478de", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
YearEnergy TypePrice Per Unit
02016Water0.050000
12017Water0.055000
22018Water0.060500
32019Water0.066550
42020Water0.073205
52016Gas1.000000
62017Gas1.100000
72018Gas1.210000
82019Gas1.331000
92020Gas1.464100
102016Electricity0.080000
112017Electricity0.088000
122018Electricity0.096800
132019Electricity0.106480
142020Electricity0.117128
\n", "
" ], "text/plain": [ " Year Energy Type Price Per Unit\n", "0 2016 Water 0.050000\n", "1 2017 Water 0.055000\n", "2 2018 Water 0.060500\n", "3 2019 Water 0.066550\n", "4 2020 Water 0.073205\n", "5 2016 Gas 1.000000\n", "6 2017 Gas 1.100000\n", "7 2018 Gas 1.210000\n", "8 2019 Gas 1.331000\n", "9 2020 Gas 1.464100\n", "10 2016 Electricity 0.080000\n", "11 2017 Electricity 0.088000\n", "12 2018 Electricity 0.096800\n", "13 2019 Electricity 0.106480\n", "14 2020 Electricity 0.117128" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rate=pd.read_excel(r\"data/Energy Consumptions Dataset.xlsx\",sheet_name=\"Rates\")\n", "rate" ] }, { "cell_type": "code", "execution_count": null, "id": "c078b84d", "metadata": {}, "outputs": [], "source": [ "rate.columns =rate.columns.str.lower()" ] }, { "cell_type": "code", "execution_count": null, "id": "df1d855e", "metadata": {}, "outputs": [], "source": [ "rate[\"price per unit\"]=round(rate[\"price per unit\"],3)" ] }, { "cell_type": "code", "execution_count": null, "id": "e05b51f8", "metadata": {}, "outputs": [], "source": [ "rate[\"energy type\"]=rate[\"energy type\"].str.lower()" ] }, { "cell_type": "code", "execution_count": null, "id": "8b09396a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearenergy typeprice per unit
02016water0.050
12017water0.055
22018water0.061
32019water0.067
42020water0.073
52016gas1.000
62017gas1.100
72018gas1.210
82019gas1.331
92020gas1.464
102016electricity0.080
112017electricity0.088
122018electricity0.097
132019electricity0.106
142020electricity0.117
\n", "
" ], "text/plain": [ " year energy type price per unit\n", "0 2016 water 0.050\n", "1 2017 water 0.055\n", "2 2018 water 0.061\n", "3 2019 water 0.067\n", "4 2020 water 0.073\n", "5 2016 gas 1.000\n", "6 2017 gas 1.100\n", "7 2018 gas 1.210\n", "8 2019 gas 1.331\n", "9 2020 gas 1.464\n", "10 2016 electricity 0.080\n", "11 2017 electricity 0.088\n", "12 2018 electricity 0.097\n", "13 2019 electricity 0.106\n", "14 2020 electricity 0.117" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rate" ] }, { "cell_type": "code", "execution_count": null, "id": "cbabb2c9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
BuildingCityCountry
0B1000New YorkUSA
1B1001New YorkUSA
2B1002New YorkUSA
3B1003Los AngelesUSA
4B1004ChicagoUSA
5B1005HoustonUSA
6B1006PhoenixUSA
7B1007ChicagoUSA
8B1008ChicagoUSA
9B1009Los AngelesUSA
10B1010Los AngelesUSA
\n", "
" ], "text/plain": [ " Building City Country\n", "0 B1000 New York USA\n", "1 B1001 New York USA\n", "2 B1002 New York USA\n", "3 B1003 Los Angeles USA\n", "4 B1004 Chicago USA\n", "5 B1005 Houston USA\n", "6 B1006 Phoenix USA\n", "7 B1007 Chicago USA\n", "8 B1008 Chicago USA\n", "9 B1009 Los Angeles USA\n", "10 B1010 Los Angeles USA" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "building=pd.read_excel(r\"data/Energy Consumptions Dataset.xlsx\",sheet_name=\"Building Master\")\n", "building" ] }, { "cell_type": "code", "execution_count": null, "id": "5e73f84d", "metadata": {}, "outputs": [], "source": [ "building.columns =building.columns.str.lower()" ] }, { "cell_type": "code", "execution_count": null, "id": "5c94eb9d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingcitycountry
0B1000New YorkUSA
1B1001New YorkUSA
2B1002New YorkUSA
3B1003Los AngelesUSA
4B1004ChicagoUSA
5B1005HoustonUSA
6B1006PhoenixUSA
7B1007ChicagoUSA
8B1008ChicagoUSA
9B1009Los AngelesUSA
10B1010Los AngelesUSA
\n", "
" ], "text/plain": [ " building city country\n", "0 B1000 New York USA\n", "1 B1001 New York USA\n", "2 B1002 New York USA\n", "3 B1003 Los Angeles USA\n", "4 B1004 Chicago USA\n", "5 B1005 Houston USA\n", "6 B1006 Phoenix USA\n", "7 B1007 Chicago USA\n", "8 B1008 Chicago USA\n", "9 B1009 Los Angeles USA\n", "10 B1010 Los Angeles USA" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "building" ] }, { "cell_type": "code", "execution_count": null, "id": "2f1ef667", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "528" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "consumption.to_sql(\"consumption\", engine, schema=\"energy\", if_exists=\"replace\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "bfa31a57", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "15" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rate.to_sql(\"rate\", engine, schema=\"energy\", if_exists=\"replace\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "c1bf6701", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "11" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "building.to_sql(\"building\", engine, schema=\"energy\", if_exists=\"replace\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "595cff18", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datebuildingwater consumptionelectricity consumptiongas consumption
2016-01-01 00:00:00B1000346159388193378
2016-01-01 00:00:00B1001281717462522187
2016-01-01 00:00:00B1002259530410913173
2016-01-01 00:00:00B1003309176255773581
2016-01-01 00:00:00B1004257742339174709
2016-01-01 00:00:00B1005276221356982810
2016-01-01 00:00:00B1006401399354583660
2016-01-01 00:00:00B1007413543290612149
2016-01-01 00:00:00B1008343048306664075
2016-01-01 00:00:00B1009412804235413263
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+---------------------+----------+-------------------+-------------------------+-----------------+\n", "| date | building | water consumption | electricity consumption | gas consumption |\n", "+---------------------+----------+-------------------+-------------------------+-----------------+\n", "| 2016-01-01 00:00:00 | B1000 | 346159 | 38819 | 3378 |\n", "| 2016-01-01 00:00:00 | B1001 | 281717 | 46252 | 2187 |\n", "| 2016-01-01 00:00:00 | B1002 | 259530 | 41091 | 3173 |\n", "| 2016-01-01 00:00:00 | B1003 | 309176 | 25577 | 3581 |\n", "| 2016-01-01 00:00:00 | B1004 | 257742 | 33917 | 4709 |\n", "| 2016-01-01 00:00:00 | B1005 | 276221 | 35698 | 2810 |\n", "| 2016-01-01 00:00:00 | B1006 | 401399 | 35458 | 3660 |\n", "| 2016-01-01 00:00:00 | B1007 | 413543 | 29061 | 2149 |\n", "| 2016-01-01 00:00:00 | B1008 | 343048 | 30666 | 4075 |\n", "| 2016-01-01 00:00:00 | B1009 | 412804 | 23541 | 3263 |\n", "+---------------------+----------+-------------------+-------------------------+-----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "33b7dc5a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption \n", "alter column \"date\" type date\n", "USING \"date\"::DATE" ] }, { "cell_type": "code", "execution_count": null, "id": "f8aa5653", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datebuildingwater consumptionelectricity consumptiongas consumption
2016-01-01B1000346159388193378
2016-01-01B1001281717462522187
2016-01-01B1002259530410913173
2016-01-01B1003309176255773581
2016-01-01B1004257742339174709
2016-01-01B1005276221356982810
2016-01-01B1006401399354583660
2016-01-01B1007413543290612149
2016-01-01B1008343048306664075
2016-01-01B1009412804235413263
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------------+----------+-------------------+-------------------------+-----------------+\n", "| date | building | water consumption | electricity consumption | gas consumption |\n", "+------------+----------+-------------------+-------------------------+-----------------+\n", "| 2016-01-01 | B1000 | 346159 | 38819 | 3378 |\n", "| 2016-01-01 | B1001 | 281717 | 46252 | 2187 |\n", "| 2016-01-01 | B1002 | 259530 | 41091 | 3173 |\n", "| 2016-01-01 | B1003 | 309176 | 25577 | 3581 |\n", "| 2016-01-01 | B1004 | 257742 | 33917 | 4709 |\n", "| 2016-01-01 | B1005 | 276221 | 35698 | 2810 |\n", "| 2016-01-01 | B1006 | 401399 | 35458 | 3660 |\n", "| 2016-01-01 | B1007 | 413543 | 29061 | 2149 |\n", "| 2016-01-01 | B1008 | 343048 | 30666 | 4075 |\n", "| 2016-01-01 | B1009 | 412804 | 23541 | 3263 |\n", "+------------+----------+-------------------+-------------------------+-----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "ec39de4b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption\n", "rename \"water consumption\" to water_consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "723fff4d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption\n", "rename \"electricity consumption\" to electricity_consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "f9cc4f8e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption\n", "rename \"gas consumption\" to gas_consumption" ] }, { "cell_type": "code", "execution_count": null, "id": "fad4b5c5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
datebuildingwater_consumptionelectricity_consumptiongas_consumption
2016-01-01B1000346159388193378
2016-01-01B1001281717462522187
2016-01-01B1002259530410913173
2016-01-01B1003309176255773581
2016-01-01B1004257742339174709
2016-01-01B1005276221356982810
2016-01-01B1006401399354583660
2016-01-01B1007413543290612149
2016-01-01B1008343048306664075
2016-01-01B1009412804235413263
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------------+----------+-------------------+-------------------------+-----------------+\n", "| date | building | water_consumption | electricity_consumption | gas_consumption |\n", "+------------+----------+-------------------+-------------------------+-----------------+\n", "| 2016-01-01 | B1000 | 346159 | 38819 | 3378 |\n", "| 2016-01-01 | B1001 | 281717 | 46252 | 2187 |\n", "| 2016-01-01 | B1002 | 259530 | 41091 | 3173 |\n", "| 2016-01-01 | B1003 | 309176 | 25577 | 3581 |\n", "| 2016-01-01 | B1004 | 257742 | 33917 | 4709 |\n", "| 2016-01-01 | B1005 | 276221 | 35698 | 2810 |\n", "| 2016-01-01 | B1006 | 401399 | 35458 | 3660 |\n", "| 2016-01-01 | B1007 | 413543 | 29061 | 2149 |\n", "| 2016-01-01 | B1008 | 343048 | 30666 | 4075 |\n", "| 2016-01-01 | B1009 | 412804 | 23541 | 3263 |\n", "+------------+----------+-------------------+-------------------------+-----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.consumption" ] }, { "cell_type": "markdown", "id": "bb634d5f", "metadata": {}, "source": [ "---------" ] }, { "cell_type": "code", "execution_count": null, "id": "83fc5a9f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearenergy typeprice per unit
2016water0.05
2017water0.055
2018water0.061
2019water0.067
2020water0.073
2016gas1.0
2017gas1.1
2018gas1.21
2019gas1.331
2020gas1.464
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------+-------------+----------------+\n", "| year | energy type | price per unit |\n", "+------+-------------+----------------+\n", "| 2016 | water | 0.05 |\n", "| 2017 | water | 0.055 |\n", "| 2018 | water | 0.061 |\n", "| 2019 | water | 0.067 |\n", "| 2020 | water | 0.073 |\n", "| 2016 | gas | 1.0 |\n", "| 2017 | gas | 1.1 |\n", "| 2018 | gas | 1.21 |\n", "| 2019 | gas | 1.331 |\n", "| 2020 | gas | 1.464 |\n", "+------+-------------+----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.rate" ] }, { "cell_type": "code", "execution_count": null, "id": "35714895", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.rate\n", "rename \"energy type\" to energy_type" ] }, { "cell_type": "code", "execution_count": null, "id": "c68143d2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.rate\n", "rename \"price per unit\" to price_per_unit" ] }, { "cell_type": "code", "execution_count": null, "id": "a12ac32c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearenergy_typeprice_per_unit
2016water0.05
2017water0.055
2018water0.061
2019water0.067
2020water0.073
2016gas1.0
2017gas1.1
2018gas1.21
2019gas1.331
2020gas1.464
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------+-------------+----------------+\n", "| year | energy_type | price_per_unit |\n", "+------+-------------+----------------+\n", "| 2016 | water | 0.05 |\n", "| 2017 | water | 0.055 |\n", "| 2018 | water | 0.061 |\n", "| 2019 | water | 0.067 |\n", "| 2020 | water | 0.073 |\n", "| 2016 | gas | 1.0 |\n", "| 2017 | gas | 1.1 |\n", "| 2018 | gas | 1.21 |\n", "| 2019 | gas | 1.331 |\n", "| 2020 | gas | 1.464 |\n", "+------+-------------+----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.rate" ] }, { "cell_type": "markdown", "id": "186eefe0", "metadata": {}, "source": [ "----------" ] }, { "cell_type": "code", "execution_count": null, "id": "4c135446", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption\n", "add constraint key1 primary key (date,building) " ] }, { "cell_type": "code", "execution_count": null, "id": "184a1731", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.rate\n", "add constraint key2 \n", "primary key (year,energy_type)" ] }, { "cell_type": "code", "execution_count": null, "id": "383d9260", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.building\n", "add constraint key3\n", "primary key (building)" ] }, { "cell_type": "code", "execution_count": null, "id": "13f3ded5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "alter table energy.consumption\n", "add constraint fk\n", "foreign key (building) references energy.building (building)" ] }, { "cell_type": "markdown", "id": "4cdf2161", "metadata": {}, "source": [ "---------" ] }, { "cell_type": "markdown", "id": "4807bdfb", "metadata": {}, "source": [ "### Unpivoting" ] }, { "cell_type": "code", "execution_count": null, "id": "807fa7ac", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "create view energy.unpivoted as\n", "with unpivoted as (\n", " select date, building, 'water' as type, water_consumption as consumption\n", " from energy.consumption\n", " union all\n", " select date, building, 'electricity' as type, electricity_consumption as consumption\n", " from energy.consumption\n", " union all\n", " select date, building, 'gas' as type, gas_consumption as consumption\n", " from energy.consumption\n", ")\n", "select * from unpivoted" ] }, { "cell_type": "markdown", "id": "297b02f2", "metadata": {}, "source": [ "------------" ] }, { "cell_type": "markdown", "id": "7f5d4628", "metadata": {}, "source": [ "- What is the total consumption for each energy type?" ] }, { "cell_type": "code", "execution_count": null, "id": "4ac19b7e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
typesum_consumption
water186245327
electricity21666978
gas2553088
" ], "text/plain": [ "+-------------+-----------------+\n", "| type | sum_consumption |\n", "+-------------+-----------------+\n", "| water | 186245327 |\n", "| electricity | 21666978 |\n", "| gas | 2553088 |\n", "+-------------+-----------------+" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select type,sum(consumption) sum_consumption\n", "from energy.unpivoted\n", "group by type\n", "order by sum(consumption) desc" ] }, { "cell_type": "markdown", "id": "9fedf7d0", "metadata": {}, "source": [ "- Which building consumes the most energy?" ] }, { "cell_type": "code", "execution_count": null, "id": "ca80b644", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingsum_consumption
B100620105096
" ], "text/plain": [ "+----------+-----------------+\n", "| building | sum_consumption |\n", "+----------+-----------------+\n", "| B1006 | 20105096 |\n", "+----------+-----------------+" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select building,sum(consumption) sum_consumption\n", "from energy.unpivoted\n", "group by building\n", "order by sum(consumption) desc\n", "limit 1" ] }, { "cell_type": "markdown", "id": "09c44d8c", "metadata": {}, "source": [ "- Which city consumes the most energy?" ] }, { "cell_type": "code", "execution_count": null, "id": "4c0450fe", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citysum_consumption
New York57287047
" ], "text/plain": [ "+----------+-----------------+\n", "| city | sum_consumption |\n", "+----------+-----------------+\n", "| New York | 57287047 |\n", "+----------+-----------------+" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select city,sum(consumption) sum_consumption\n", "from energy.unpivoted\n", "join energy.building using (building)\n", "group by city\n", "order by sum(consumption) desc\n", "limit 1" ] }, { "cell_type": "markdown", "id": "70e4a811", "metadata": {}, "source": [ "- What is the average consumption per building?" ] }, { "cell_type": "code", "execution_count": null, "id": "56edff5c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingavg_consumption
B1006139618.72
B1008138291.58
B1000135664.33
B1001135541.89
B1003135480.66
B1005134739.98
B1010134521.78
B1004133191.37
B1002126620.50
B1007123975.74
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+----------+-----------------+\n", "| building | avg_consumption |\n", "+----------+-----------------+\n", "| B1006 | 139618.72 |\n", "| B1008 | 138291.58 |\n", "| B1000 | 135664.33 |\n", "| B1001 | 135541.89 |\n", "| B1003 | 135480.66 |\n", "| B1005 | 134739.98 |\n", "| B1010 | 134521.78 |\n", "| B1004 | 133191.37 |\n", "| B1002 | 126620.50 |\n", "| B1007 | 123975.74 |\n", "+----------+-----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select building,round(avg(consumption),2) avg_consumption\n", "from energy.unpivoted\n", "group by building\n", "order by avg(consumption) desc" ] }, { "cell_type": "markdown", "id": "6ce9b7e2", "metadata": {}, "source": [ "- What is the total energy cost by energy type?" ] }, { "cell_type": "code", "execution_count": null, "id": "0fd3c7c8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
typetotal_cost
water10850452.45
gas3011010.87
electricity2023591.90
" ], "text/plain": [ "+-------------+-------------+\n", "| type | total_cost |\n", "+-------------+-------------+\n", "| water | 10850452.45 |\n", "| gas | 3011010.87 |\n", "| electricity | 2023591.90 |\n", "+-------------+-------------+" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select type,round(sum(consumption * price_per_unit)::numeric,2) as total_cost\n", "from energy.unpivoted\n", "join energy.rate\n", "on energy.unpivoted.type=energy.rate.energy_type\n", "and extract(year from energy.unpivoted.date)=energy.rate.year\n", "group by type\n", "order by total_cost desc" ] }, { "cell_type": "markdown", "id": "a94e94f8", "metadata": {}, "source": [ "- Which building has the highest energy cost?" ] }, { "cell_type": "code", "execution_count": null, "id": "45082503", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingtotal_cost
B10081501077.76
" ], "text/plain": [ "+----------+------------+\n", "| building | total_cost |\n", "+----------+------------+\n", "| B1008 | 1501077.76 |\n", "+----------+------------+" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select building ,round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", "from energy.unpivoted\n", "join energy.rate\n", "on energy.rate.energy_type = energy.unpivoted.type\n", "and extract(year from energy.unpivoted.date) = energy.rate.year\n", "group by building\n", "order by total_cost desc\n", "limit 1" ] }, { "cell_type": "markdown", "id": "8e4ed2bc", "metadata": {}, "source": [ "- Which city has the highest energy cost?" ] }, { "cell_type": "code", "execution_count": null, "id": "560b6626", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citytotal_cost
New York4346806.64
" ], "text/plain": [ "+----------+------------+\n", "| city | total_cost |\n", "+----------+------------+\n", "| New York | 4346806.64 |\n", "+----------+------------+" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select city ,round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", "from energy.unpivoted\n", "join energy.rate\n", "on energy.rate.energy_type = energy.unpivoted.type\n", "and extract(year from energy.unpivoted.date) = energy.rate.year\n", "join energy.building\n", "on energy.building.building=energy.unpivoted.building\n", "group by city\n", "order by total_cost desc\n", "limit 1" ] }, { "cell_type": "markdown", "id": "ad8e2a6b", "metadata": {}, "source": [ "- Which buildings have an energy cost higher than the average?" ] }, { "cell_type": "code", "execution_count": null, "id": "79df8299", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingtotal_cost
B10081501077.76
B10061483114.90
B10011481860.38
B10001472347.82
B10031465989.03
B10101462799.11
B10041457702.69
B10051444779.47
" ], "text/plain": [ "+----------+------------+\n", "| building | total_cost |\n", "+----------+------------+\n", "| B1008 | 1501077.76 |\n", "| B1006 | 1483114.90 |\n", "| B1001 | 1481860.38 |\n", "| B1000 | 1472347.82 |\n", "| B1003 | 1465989.03 |\n", "| B1010 | 1462799.11 |\n", "| B1004 | 1457702.69 |\n", "| B1005 | 1444779.47 |\n", "+----------+------------+" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "with total_cost_per_building as(\n", " select building ,round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", " from energy.unpivoted\n", " join energy.rate\n", " on energy.rate.energy_type = energy.unpivoted.type\n", " and extract(year from energy.unpivoted.date) = energy.rate.year\n", " group by building\n", ")\n", "select building, total_cost\n", "from total_cost_per_building\n", "where total_cost > (select avg(total_cost)from total_cost_per_building)\n", "order by total_cost desc" ] }, { "cell_type": "markdown", "id": "634ca29a", "metadata": {}, "source": [ "- What is the most expensive energy type for each year?" ] }, { "cell_type": "code", "execution_count": null, "id": "b3d79bac", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yeartypetotal_costrank_in_year
2016water2259326.301
2017water2638119.111
2018water2889609.591
2019water3063397.451
" ], "text/plain": [ "+------+-------+------------+--------------+\n", "| year | type | total_cost | rank_in_year |\n", "+------+-------+------------+--------------+\n", "| 2016 | water | 2259326.30 | 1 |\n", "| 2017 | water | 2638119.11 | 1 |\n", "| 2018 | water | 2889609.59 | 1 |\n", "| 2019 | water | 3063397.45 | 1 |\n", "+------+-------+------------+--------------+" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "with yearly_ranked as (\n", " select year, type, total_cost,\n", " rank() over (partition by year order by total_cost desc) as rank_in_year\n", " from (\n", " select year, type, round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", " from energy.rate\n", " join energy.unpivoted\n", " on energy.rate.energy_type = energy.unpivoted.type\n", " and extract(year from energy.unpivoted.date) = energy.rate.year\n", " group by year, type\n", " ) as yearly_costs\n", ")\n", "select year, type, total_cost,rank_in_year\n", "from yearly_ranked\n", "where rank_in_year =1\n", "order by year;" ] }, { "cell_type": "markdown", "id": "f2e98ab4", "metadata": {}, "source": [ "--------" ] }, { "cell_type": "markdown", "id": "10f4d5d9", "metadata": {}, "source": [ "- What's the average consumption per month across all years for each energy type?" ] }, { "cell_type": "code", "execution_count": null, "id": "e241572f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearmonthtypeavg_consumption
20161water338062.45
20161electricity33960.91
20161gas3277.45
20162electricity37415.36
20162water331177.09
20162gas3760.82
20163water355865.27
20163electricity33522.64
20163gas3413.73
20164gas3863.00
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------+-------+-------------+-----------------+\n", "| year | month | type | avg_consumption |\n", "+------+-------+-------------+-----------------+\n", "| 2016 | 1 | water | 338062.45 |\n", "| 2016 | 1 | electricity | 33960.91 |\n", "| 2016 | 1 | gas | 3277.45 |\n", "| 2016 | 2 | electricity | 37415.36 |\n", "| 2016 | 2 | water | 331177.09 |\n", "| 2016 | 2 | gas | 3760.82 |\n", "| 2016 | 3 | water | 355865.27 |\n", "| 2016 | 3 | electricity | 33522.64 |\n", "| 2016 | 3 | gas | 3413.73 |\n", "| 2016 | 4 | gas | 3863.00 |\n", "+------+-------+-------------+-----------------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select extract(year from energy.unpivoted.date) as year,\n", "extract(month from energy.unpivoted.date) as month,\n", "type,\n", "round(avg(consumption),2) as avg_consumption\n", "from energy.unpivoted\n", "group by year,month,type\n", "order by year,month" ] }, { "cell_type": "markdown", "id": "0475f8c7", "metadata": {}, "source": [ "- Which building has the lowest energy consumption?" ] }, { "cell_type": "code", "execution_count": null, "id": "a76ed042", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
buildingsum_consumption
B100917844291
" ], "text/plain": [ "+----------+-----------------+\n", "| building | sum_consumption |\n", "+----------+-----------------+\n", "| B1009 | 17844291 |\n", "+----------+-----------------+" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select building, sum(consumption) as sum_consumption\n", "from energy.unpivoted\n", "group by building\n", "order by sum_consumption\n", "limit 1" ] }, { "cell_type": "markdown", "id": "edfd57cb", "metadata": {}, "source": [ "- Which city has the lowest energy cost?" ] }, { "cell_type": "code", "execution_count": null, "id": "b67d406c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
citytotal_cost
Houston1444779.47
" ], "text/plain": [ "+---------+------------+\n", "| city | total_cost |\n", "+---------+------------+\n", "| Houston | 1444779.47 |\n", "+---------+------------+" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select city ,round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", "from energy.unpivoted\n", "join energy.rate\n", "on energy.rate.energy_type = energy.unpivoted.type\n", "and extract(year from energy.unpivoted.date) = energy.rate.year\n", "join energy.building\n", "on energy.building.building=energy.unpivoted.building\n", "group by city\n", "order by total_cost\n", "limit 1" ] }, { "cell_type": "markdown", "id": "5bc7eb9f", "metadata": {}, "source": [ "-----" ] }, { "cell_type": "markdown", "id": "9b1c7a5c", "metadata": {}, "source": [ "### monthly_energy_summary" ] }, { "cell_type": "code", "execution_count": null, "id": "c6ad9788", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "create view energy.monthly_energy_summary as\n", "with monthly_summary as(\n", " select \n", " extract(year from energy.unpivoted.date) as year,\n", " extract(month from energy.unpivoted.date) as month,\n", " round(sum(consumption)) as total_consumption,\n", " round(sum(consumption * price_per_unit)::numeric, 2) as total_cost\n", " from energy.unpivoted\n", " join energy.rate\n", " on energy.rate.energy_type = energy.unpivoted.type\n", " and extract(year from energy.unpivoted.date) = energy.rate.year\n", " group by extract(year from energy.unpivoted.date), extract(month from energy.unpivoted.date)\n", ")\n", "select * from monthly_summary\n", "order by year desc,month desc\n", "limit 1" ] }, { "cell_type": "code", "execution_count": null, "id": "770ab566", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearmonthtotal_consumptiontotal_cost
2019124198595376458.28
" ], "text/plain": [ "+------+-------+-------------------+------------+\n", "| year | month | total_consumption | total_cost |\n", "+------+-------+-------------------+------------+\n", "| 2019 | 12 | 4198595 | 376458.28 |\n", "+------+-------+-------------------+------------+" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.monthly_energy_summary" ] }, { "cell_type": "markdown", "id": "4b769dbf", "metadata": {}, "source": [ "--------" ] }, { "cell_type": "markdown", "id": "0ca982ef", "metadata": {}, "source": [ "### dataset_calendar" ] }, { "cell_type": "code", "execution_count": null, "id": "319a5b85", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "create view energy.dataset_calendar as\n", "with dataset_calendar as (\n", " select \n", " generate_series(\n", " (select min(date) from energy.unpivoted),\n", " (select max(date) from energy.unpivoted),\n", " interval '1 day'\n", " )::date as date\n", ")\n", "select\n", " date,\n", " extract(year from date) as year,\n", " extract(month from date) as month,\n", " extract(day from date) as day,\n", " trim(to_char(date, 'Month')) as month_name,\n", " trim(to_char(date, 'Day')) as day_name\n", "from dataset_calendar" ] }, { "cell_type": "code", "execution_count": null, "id": "3ba56806", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dateyearmonthdaymonth_nameday_name
2016-01-01201611JanuaryFriday
2016-01-02201612JanuarySaturday
2016-01-03201613JanuarySunday
2016-01-04201614JanuaryMonday
2016-01-05201615JanuaryTuesday
2016-01-06201616JanuaryWednesday
2016-01-07201617JanuaryThursday
2016-01-08201618JanuaryFriday
2016-01-09201619JanuarySaturday
2016-01-102016110JanuarySunday
\n", "Truncated to displaylimit of 10." ], "text/plain": [ "+------------+------+-------+-----+------------+-----------+\n", "| date | year | month | day | month_name | day_name |\n", "+------------+------+-------+-----+------------+-----------+\n", "| 2016-01-01 | 2016 | 1 | 1 | January | Friday |\n", "| 2016-01-02 | 2016 | 1 | 2 | January | Saturday |\n", "| 2016-01-03 | 2016 | 1 | 3 | January | Sunday |\n", "| 2016-01-04 | 2016 | 1 | 4 | January | Monday |\n", "| 2016-01-05 | 2016 | 1 | 5 | January | Tuesday |\n", "| 2016-01-06 | 2016 | 1 | 6 | January | Wednesday |\n", "| 2016-01-07 | 2016 | 1 | 7 | January | Thursday |\n", "| 2016-01-08 | 2016 | 1 | 8 | January | Friday |\n", "| 2016-01-09 | 2016 | 1 | 9 | January | Saturday |\n", "| 2016-01-10 | 2016 | 1 | 10 | January | Sunday |\n", "+------------+------+-------+-----+------------+-----------+\n", "Truncated to displaylimit of 10." ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "select * from energy.dataset_calendar" ] }, { "cell_type": "markdown", "id": "27129496", "metadata": {}, "source": [ "---------" ] } ], "metadata": { "kernelspec": { "display_name": "base", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.6" } }, "nbformat": 4, "nbformat_minor": 5 }