postgresql_action

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 '%佳莲%'

sudo -u postgres createuser --superuser dbuser
sudo -u postgres psql

ubuntu@VM-52-248-ubuntu:/etc/init.d$ sudo -u postgres createuser --superuser dbuser
ubuntu@VM-52-248-ubuntu:/etc/init.d$ sudo -u postgres psql
psql (9.5.6)
Type "help" for help.

postgres=# show databases
postgres-# create database wdb
postgres-# show databases
postgres-# ;
ERROR:  syntax error at or near "create"
LINE 2: 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 2: w int,
        ^
postgres=# create table wtb
 (w int,
wb smallint);
CREATE TABLE
postgres=# insert into wtb values (23,34);
INSERT 0 1
postgres=# select * from wtb;
 w  | wb
----+----
 23 | 34
(1 row)

postgres=#

apt install postgresql

ubuntu@VM-52-248-ubuntu:~/postgresql$ pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.7.1-cp27-cp27mu-manylinux1_x86_64.whl (2.7MB)
    100% |################################| 2.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 215, in main
    status = self.run(options, args)
  File "/usr/local/lib/python2.7/dist-packages/pip/commands/install.py", line 342, in run
    prefix=options.prefix_path,
  File "/usr/local/lib/python2.7/dist-packages/pip/req/req_set.py", line 784, in install
    **kwargs
  File "/usr/local/lib/python2.7/dist-packages/pip/req/req_install.py", line 851, 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 1064, in move_wheel_files
    isolated=self.isolated,
  File "/usr/local/lib/python2.7/dist-packages/pip/wheel.py", line 345, in move_wheel_files
    clobber(source, lib_dir, True)
  File "/usr/local/lib/python2.7/dist-packages/pip/wheel.py", line 316, in clobber
    ensure_dir(destdir)
  File "/usr/local/lib/python2.7/dist-packages/pip/utils/__init__.py", line 83, in ensure_dir
    os.makedirs(path)
  File "/usr/lib/python2.7/os.py", line 157, in makedirs
    mkdir(name, mode)
OSError: [Errno 13] Permission denied: '/usr/local/lib/python2.7/dist-packages/psycopg2-2.7.1.dist-info'
ubuntu@VM-52-248-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=4, 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=15)')': /simple/psycopg2/
  Downloading psycopg2-2.7.1-cp27-cp27mu-manylinux1_x86_64.whl (2.7MB)
    100% |################################| 2.7MB 178kB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.7.1
ubuntu@VM-52-248-ubuntu:~/postgresql$


SELECT COUNT(1) FROM questionable_mac;

SELECT detail_data->'wifi_list'->3 FROM questionable_mac;


SELECT * FROM (
SELECT detail_data->>'wifi_list' AS mac_list FROM questionable_mac) AS tmp WHERE mac_list LIKE  '%zzzzzz%';








CREATE TABLE control_group_1200k
(
  oid_timestamp VARCHAR(64) NOT NULL PRIMARY KEY,
  detail_data   VARCHAR(1024)
);

DROP TABLE  questionable_mac  ;
CREATE TABLE questionable_mac
(
  mac                        CHAR(17)  NOT NULL PRIMARY KEY,
  detail_data                JSON,
  tbl_cp_signal_wifi_id_list VARCHAR(10240)
);

SELECT pdata
FROM (

       SELECT
         detail_data -> 'data'                 AS pdata,
         detail_data -> 'data' ->> 'timestamp' AS ptimestamp
       FROM apiv2_single_mac_with_res) tmp

ORDER BY ptimestamp DESC
LIMIT 200;

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 ;

创建视图 CREATE VIEW

类型转换 CAST

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;