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
w
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=#
apt install postgresql
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$
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;
