2D.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "c7b6eade",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import itertools\n",
    "import random\n",
    "\n",
    "# schema: R(A,B,C)\n",
    "# query: select agg(C) from R where T.A=2000 and T.B=30;\n",
    "\n",
    "dom_A = list(range(2000, 2024 + 1))\n",
    "dom_B = list(range(18, 40 + 1))\n",
    "doms=[dom_A,dom_B]\n",
    "\n",
    "print()\n",
    "\n",
    "def create_db(size): \n",
    "    A = random.choices(range(2000,2024),k=size)\n",
    "    B = random.choices(range(18,41),k=size)\n",
    "    C = np.random.normal(1000,200,size)\n",
    "    data = {'A':A,'B':B,'C':C}\n",
    "    return pd.DataFrame(data)\n",
    "\n",
    "table=create_db(10000)\n",
    "\n",
    "true_dom_A = list(table['A'].drop_duplicates().values)\n",
    "true_dom_B = list(table['B'].drop_duplicates().values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "3b061de7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_query(table,cond_A,cond_B,agg_C):\n",
    "    if '=' in cond_A: \n",
    "        table=table[table['A']==int(cond_A.split('=')[1])]\n",
    "    if '=' in cond_B: \n",
    "        table=table[table['B']==int(cond_B.split('=')[1])]\n",
    "    if '<' in cond_A:\n",
    "        if '<=' in cond_A:\n",
    "            table=table[table['A']<=int(cond_A.split('<=')[1])]\n",
    "        else: \n",
    "            table=table[table['A']<int(cond_A.split('<')[1])]\n",
    "    if '>' in cond_A:\n",
    "        if '>=' in cond_A:\n",
    "            table=table[table['A']>=int(cond_A.split('>=')[1])]\n",
    "        else: \n",
    "            table=table[table['A']>int(cond_A.split('>')[1])]\n",
    "    if '<' in cond_B:\n",
    "        if '<=' in cond_B:\n",
    "            table=table[table['B']<=int(cond_B.split('<=')[1])]\n",
    "        else: \n",
    "            table=table[table['B']<int(cond_B.split('<')[1])]\n",
    "    if '>' in cond_B:\n",
    "        if '>=' in cond_B:\n",
    "            table=table[table['B']>=int(cond_B.split('>=')[1])]\n",
    "        else: \n",
    "            table=table[table['B']>int(cond_B.split('>')[1])]\n",
    "    result = 0\n",
    "    if agg_C=='sum':\n",
    "        result=table['C'].sum()\n",
    "    if agg_C=='avg':\n",
    "        result=table['C'].avg()\n",
    "    if agg_C=='count':\n",
    "        result=table['C'].count()\n",
    "    return result\n",
    "\n",
    "#run_query(table,'A>2017','B>=34','sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "7667d932",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "query:   select sum(C) from R where T.A=2018 and T.B=23;\n",
      "correct result: 23893.765163\n",
      "query_p: select sum(C) from R where T.A=2001 and T.B=39;\n",
      "query_p: select sum(C) from R where T.A=2015 and T.B=37;\n",
      "query_p: select sum(C) from R where T.A=2010 and T.B=37;\n",
      "query_p: select sum(C) from R where T.A=2003 and T.B=40;\n",
      "query_p: select sum(C) from R where T.A=2011 and T.B=21;\n",
      "query_p: select sum(C) from R where T.A=2003 and T.B=38;\n",
      "query_p: select sum(C) from R where T.A=2020 and T.B=40;\n",
      "query_p: select sum(C) from R where T.A=2018 and T.B=22;\n",
      "query_p: select sum(C) from R where T.A=2020 and T.B=18;\n",
      "query_p: select sum(C) from R where T.A=2016 and T.B=38;\n",
      "query_p: select sum(C) from R where T.A=2009 and T.B=39;\n",
      "query_p: select sum(C) from R where T.A=2023 and T.B=24;\n",
      "query_p: select sum(C) from R where T.A=2011 and T.B=38;\n",
      "query_p: select sum(C) from R where T.A=2015 and T.B=23;\n",
      "query_p: select sum(C) from R where T.A=2005 and T.B=34;\n",
      "query_p: select sum(C) from R where T.A=2021 and T.B=23;\n",
      "query_p: select sum(C) from R where T.A=2016 and T.B=18;\n",
      "query_p: select sum(C) from R where T.A=2023 and T.B=33;\n",
      "query_p: select sum(C) from R where T.A=2007 and T.B=33;\n",
      "query_p: select sum(C) from R where T.A=2024 and T.B=33;\n",
      "query_p: select sum(C) from R where T.A=2007 and T.B=23;\n",
      "query_p: select sum(C) from R where T.A=2013 and T.B=19;\n",
      "query_p: select sum(C) from R where T.A=2023 and T.B=35;\n",
      "query_p: select sum(C) from R where T.A=2023 and T.B=26;\n",
      "query_p: select sum(C) from R where T.A=2022 and T.B=18;\n",
      "query_p: select sum(C) from R where T.A=2022 and T.B=24;\n",
      "query_p: select sum(C) from R where T.A=2002 and T.B=27;\n",
      "query_p: select sum(C) from R where T.A=2001 and T.B=34;\n",
      "query_p: select sum(C) from R where T.A=2004 and T.B=27;\n",
      "query_p: select sum(C) from R where T.A=2006 and T.B=34;\n",
      "query_p: select sum(C) from R where T.A=2005 and T.B=32;\n",
      "query_p: select sum(C) from R where T.A=2014 and T.B=22;\n",
      "query_p: select sum(C) from R where T.A=2021 and T.B=29;\n",
      "query_p: select sum(C) from R where T.A=2011 and T.B=28;\n",
      "query_p: select sum(C) from R where T.A=2020 and T.B=35;\n",
      "query_p: select sum(C) from R where T.A=2008 and T.B=38;\n",
      "query_p: select sum(C) from R where T.A=2020 and T.B=24;\n",
      "query_p: select sum(C) from R where T.A=2001 and T.B=40;\n",
      "query_p: select sum(C) from R where T.A=2017 and T.B=34;\n",
      "query_p: select sum(C) from R where T.A=2015 and T.B=35;\n",
      "query_p: select sum(C) from R where T.A=2020 and T.B=28;\n",
      "query_p: select sum(C) from R where T.A=2016 and T.B=18;\n",
      "query_p: select sum(C) from R where T.A=2001 and T.B=18;\n",
      "query_p: select sum(C) from R where T.A=2010 and T.B=36;\n",
      "query_p: select sum(C) from R where T.A=2023 and T.B=30;\n",
      "query_p: select sum(C) from R where T.A=2005 and T.B=24;\n",
      "query_p: select sum(C) from R where T.A=2015 and T.B=39;\n",
      "query_p: select sum(C) from R where T.A=2021 and T.B=22;\n",
      "query_p: select sum(C) from R where T.A=2002 and T.B=27;\n",
      "query_p: select sum(C) from R where T.A=2012 and T.B=21;\n",
      "query_p: select sum(C) from R where T.A=2021 and T.B=24;\n",
      "error: twoD_space_all: min:219.850598 avg: 6650.616952 max: 23893.765163\n",
      "error: twoD_space_res: min:7011.426195 avg: 7011.426195 max: 7011.426195\n"
     ]
    }
   ],
   "source": [
    "def twoD_space_all(x, y): \n",
    "    return [(a, b) for a in x for b in y]\n",
    "\n",
    "def twoD_space(table, predicate_cols):\n",
    "    return list(map(tuple, table[predicate_cols].drop_duplicates().to_numpy()))\n",
    "\n",
    "def twoD_utility(space, predicate):\n",
    "    #space = twoD_space(x,y)\n",
    "    scores = [l1_distance(p1,predicate) for p1 in space]\n",
    "    # Normalize scores\n",
    "    mins = min(scores)\n",
    "    maxs = max(scores)\n",
    "    return [(s - mins)/(maxs-mins) for s in scores]\n",
    "\n",
    "def l1_distance(p1,p2):\n",
    "    return sum(abs(a - b) for a, b in zip(p1, p2))\n",
    "\n",
    "\n",
    "def twoD_exponential_mechanism(predicate,sensitivity,epsilon,space_fn):\n",
    "    # Create the space and calculate the score for each element in space\n",
    "    if space_fn==twoD_space_all:\n",
    "        space=twoD_space_all(doms[0],doms[1])\n",
    "    if space_fn==twoD_space:\n",
    "        space = twoD_space(table,['A','B'])\n",
    "    scores = twoD_utility(space, predicate)\n",
    "    \n",
    "    # Calculate the probability for each element, based on its score\n",
    "    probabilities = [np.exp(epsilon * score / (2 * sensitivity)) for score in scores]\n",
    "    \n",
    "    # Normalize the probabilties so they sum to 1\n",
    "    probabilities = probabilities / np.linalg.norm(probabilities, ord=1)\n",
    "    \n",
    "    # Choose an element from R based on the probabilities\n",
    "    return space[np.random.choice(list(range(0,len(scores))), 1, p=probabilities)[0]]\n",
    "\n",
    "def accuracy(query,query_p,agg): \n",
    "    result = run_query(table,'A='+str(query[0]),'B='+str(query[1]),agg)\n",
    "    result_p = run_query(table,'A='+str(query_p[0]),'B='+str(query_p[1]),agg)\n",
    "    return result_p - result\n",
    "\n",
    "\n",
    "def test_per_query(query,agg,count,space):\n",
    "    errs = []\n",
    "    result = run_query(table,'A='+str(query[0]),'B='+str(query[1]),agg)\n",
    "    for i in range(count): \n",
    "        query_p = twoD_exponential_mechanism(predicate, 1, 1, space)\n",
    "        print('query_p: select %s(C) from R where T.A=%d and T.B=%d;'%(agg,query_p[0],query_p[1]))\n",
    "        result_p = run_query(table,'A='+str(query_p[0]),'B='+str(query_p[1]),agg)\n",
    "        error = abs(result_p-result)\n",
    "        errs+=[error]\n",
    "    return min(errs), sum(errs)/len(errs), max(errs)\n",
    "\n",
    "def avg(l):\n",
    "    return sum(l)/len(l)\n",
    "\n",
    "def test(q_count): \n",
    "    agg = 'sum'\n",
    "    min_err_all,max_err_all,avg_err_all=[],[],[] \n",
    "    min_err_res,max_err_res,avg_err_res=[],[],[] \n",
    "    for i in range(q_count):\n",
    "        query = (random.sample(dom_A,1)[0],random.sample(dom_B,1)[0])\n",
    "        print('query:   select sum(C) from R where T.A=%d and T.B=%d;'%(query[0],query[1]))\n",
    "        print('correct result: %f'%(run_query(table,'A='+str(query[0]),'B='+str(query[1]),agg)))\n",
    "        min_err,avg_err,max_err=test_per_query(query,agg,50,twoD_space_all)\n",
    "        min_err_all+=[min_err]\n",
    "        avg_err_all+=[avg_err]\n",
    "        max_err_all+=[max_err]\n",
    "        min_err,avg_err,max_err=test_per_query(query,agg,1,twoD_space)\n",
    "        min_err_res+=[min_err]\n",
    "        avg_err_res+=[avg_err]\n",
    "        max_err_res+=[max_err]\n",
    "    print('error: twoD_space_all: min:%f avg: %f max: %f'%(avg(min_err_all),avg(avg_err_all),avg(max_err_all)))\n",
    "    print('error: twoD_space_res: min:%f avg: %f max: %f'%(avg(min_err_res),avg(avg_err_res),avg(max_err_res)))\n",
    "\n",
    "          \n",
    "test(1)\n",
    "          \n",
    "#  T.A=2000 and T.B=30\n",
    "#agg = 'sum'\n",
    "#query = (2017,34)\n",
    "#print('query:   select sum(C) from R where T.A=%d and T.B=%d;'%(query[0],query[1]))\n",
    "#print('correct result: %f'%(run_query(table,'A='+str(query[0]),'B='+str(query[1]),agg)))\n",
    "\n",
    "#min_acc,avg_acc,max_acc = test(query,agg,50,twoD_space_all)\n",
    "#print('error: twoD_space_all: min:%f avg: %f max: %f'%(min_acc,avg_acc,max_acc))\n",
    "#min_acc,avg_acc,max_acc = test(query,agg,50,twoD_space)\n",
    "#print('error: twoD_space:     min:%f avg: %f max: %f'%(min_acc,avg_acc,max_acc))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bcea51b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8249e711",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}