SELECT   *

        FROM x123_area a

        LEFT JOIN x123_user_task_brief utb ON utb.ref_area_code = a.area_code WHERE  area_name

LIKE '福田星河%' OR area_name  LIKE '%佳莲%'

ubuntu@VM---ubuntu:/etc/init.d$ sudo -u postgres createuser --superuser dbuser

ubuntu@VM---ubuntu:/etc/init.d$ sudo -u postgres psql

psql (9.5.)

Type "help" for help.

postgres=# show databases

postgres-# create database wdb

postgres-# show databases

postgres-# ;

ERROR:  syntax error at or near "create"

LINE : create database wdb

        ^

postgres=# show databases;

ERROR:  unrecognized configuration parameter "databases"

postgres=# create table wtb

postgres-# w int,

postgres-# wb smallint);

ERROR:  syntax error at or near "w"

LINE : w int,

        ^

postgres=# create table wtb

 (w int,

wb smallint);

CREATE TABLE

postgres=# insert into wtb values (,);

INSERT

postgres=# select * from wtb;

 w  | wb

----+----

  |

( row)

postgres=#

ubuntu@VM---ubuntu:~/postgresql$ pip install psycopg2

Collecting psycopg2

  Downloading psycopg2-2.7.-cp27-cp27mu-manylinux1_x86_64.whl (.7MB)

    % |################################| .7MB 112kB/s

Installing collected packages: psycopg2

Exception:

Traceback (most recent call last):

  File "/usr/local/lib/python2.7/dist-packages/pip/basecommand.py", line , in main

    status = self.run(options, args)

  File "/usr/local/lib/python2.7/dist-packages/pip/commands/install.py", line , in run

    prefix=options.prefix_path,

  File "/usr/local/lib/python2.7/dist-packages/pip/req/req_set.py", line , in install

    **kwargs

  File "/usr/local/lib/python2.7/dist-packages/pip/req/req_install.py", line , in install

    self.move_wheel_files(self.source_dir, root=root, prefix=prefix)

  File "/usr/local/lib/python2.7/dist-packages/pip/req/req_install.py", line , in move_wheel_files

    isolated=self.isolated,

  File "/usr/local/lib/python2.7/dist-packages/pip/wheel.py", line , in move_wheel_files

    clobber(source, lib_dir, True)

  File "/usr/local/lib/python2.7/dist-packages/pip/wheel.py", line , in clobber

    ensure_dir(destdir)

  File "/usr/local/lib/python2.7/dist-packages/pip/utils/__init__.py", line , in ensure_dir

    os.makedirs(path)

  File "/usr/lib/python2.7/os.py", line , in makedirs

    mkdir(name, mode)

OSError: [Errno ] Permission denied: '/usr/local/lib/python2.7/dist-packages/psycopg2-2.7.1.dist-info'

ubuntu@VM---ubuntu:~/postgresql$ sudo pip install psycopg2

The directory '/home/ubuntu/.cache/pip/http' or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.

The directory '/home/ubuntu/.cache/pip' or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.

Collecting psycopg2

  Retrying (Retry(total=, connect=None, read=None, redirect=None)) after connection broken by 'ConnectTimeoutError(<pip._vendor.requests.packages.urllib3.connection.VerifiedHTTPSConnection object at 0x7f8f0915ab90>, 'Connection to pypi.python.org timed out. (connect timeout=)')': /simple/psycopg2/

  Downloading psycopg2-2.7.-cp27-cp27mu-manylinux1_x86_64.whl (.7MB)

    % |################################| .7MB 178kB/s

Installing collected packages: psycopg2

Successfully installed psycopg2-2.7.

ubuntu@VM---ubuntu:~/postgresql$

CODE

$ awk '{c++} END {print c}' *6.csv

SELECT COUNT(1)

FROM control_group_with_compute_res;

SELECT COUNT(1)

FROM (

       SELECT

         detail_data ->> 'city' AS pcity

       FROM control_group_with_compute_res

     ) tmp

WHERE pcity = '深圳市'

SELECT COUNT(1)

FROM (

       SELECT

         detail_data ->> 'city' AS pcity,

         mac_with_final_res

       FROM control_group_with_compute_res

     ) tmp

WHERE pcity = '深圳市'

      AND mac_with_final_res IS NOT NULL

SELECT COUNT(1)

FROM questionable_mac

WHERE ref_region_id_num > 1;

SELECT COUNT(1)

FROM (

       SELECT

         detail_data ->> 'city' AS pcity,

         filter_regionmac_list

       FROM control_group_with_compute_res

     ) tmp

WHERE pcity = '深圳市'

      AND filter_regionmac_list IS NOT NULL;

SELECT COUNT(DISTINCT CONCAT(mac_with_final_res)),COUNT(1)

FROM (

       SELECT

         oid_timestamp,

         detail_data ->> 'city' AS pcity,

         mac_with_final_res

       FROM control_group_with_compute_res

     ) tmp

WHERE pcity = '深圳市'

      AND mac_with_final_res IS NOT NULL ;

DROP VIEW IF EXISTS v_cmp_original_vs_mac;

CREATE VIEW v_cmp_original_vs_mac AS

  SELECT CAST(cmp_original_vs_mac -> '500' ->> 'dis' AS FLOAT) AS dis_f

  FROM control_group_with_compute_res

  WHERE cmp_original_vs_mac IS NOT NULL;

SELECT COUNT(1)

FROM v_cmp_original_vs_mac

WHERE dis_f < 0.5

UNION ALL

SELECT COUNT(1)

FROM v_cmp_original_vs_mac

WHERE dis_f >= 0.5 AND dis_f < 1

UNION ALL

SELECT COUNT(1)

FROM v_cmp_original_vs_mac;

SELECT

  COUNT(tb_whole.f),

  COUNT(tb_sub.f)

FROM tb tb_whole

  LEFT JOIN (SELECT f

             FROM tb

             WHERE f > 10) tb_sub

    ON tb_whole.f = tb_sub.f;

SELECT

  COUNT(tb_whole.oid_timestamp),

  COUNT(tb_subset.oid_timestamp)

FROM control_group_with_compute_res tb_whole

  LEFT JOIN (SELECT oid_timestamp

             FROM control_group_with_compute_res

             WHERE detail_data ->> 'city' = '深圳市') tb_subset

    ON tb_whole.oid_timestamp = tb_subset.oid_timestamp;

SELECT

  COUNT(tb_whole.oid_timestamp),

  COUNT(DISTINCT tb_subset.oid_timestamp)

FROM control_group_with_compute_res tb_whole

  LEFT JOIN (SELECT oid_timestamp

             FROM control_group_with_compute_res

             WHERE detail_data ->> 'city' = '深圳市') tb_subset

    ON tb_whole.oid_timestamp = tb_subset.oid_timestamp;