49rGVvT3

1-- Function: public.export_sas7bdat(text, text)
2
3-- DROP FUNCTION public.export_sas7bdat(text, text);
4
5CREATE OR REPLACE FUNCTION public.export_sas7bdat(
6    tbl text DEFAULT 'p1_currency'::text,
7    path text DEFAULT '/media/sf_PinkTransfert/'::text)
8  RETURNS text AS
9$BODY$
10
11require(RPostgreSQL)
12require(haven)
13require(data.table)
14
15
16return('makes server explode out of RAM on big tables. NOT SAFE altoough brilliant')
17
18
19# NOT USED BY PL/R
20con = dbConnect(dbDriver("PostgreSQL"), user="selector", password="select01",
21                host="192.168.1.50", dbname="postgres")
22
23time_start = Sys.time()
24
25data <- dbGetQuery(con, paste0("TABLE ", tbl))
26
27time_get = Sys.time()
28pg.thrownotice(paste0('done fetch data: ', (time_get - time_start)))
29
30write_sas(data, paste0(path, tbl, '.sas7dbat'))
31fwrite(data, paste0(path, tbl, '.csv'))
32
33time_write = Sys.time()
34
35pg.thrownotice(paste0('done export data: ', (time_write - time_get) ))
36
37return(paste0(tbl, ' exported to ', path))
38
39$BODY$
40  LANGUAGE plr VOLATILE
41  COST 100;
42ALTER FUNCTION public.export_sas7bdat(text, text)
43  OWNER TO datamart;
44
45SELECT 
46  nspname AS schemaname, relname, reltuples::bigint
47FROM pg_class C
48LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
49WHERE 
50  nspname NOT IN ('pg_catalog', 'information_schema') AND
51  relkind='r' 
52ORDER BY reltuples DESC;/*
53
54CREATE UNLOGGED TABLE temp_neolaneid_sample AS
55	SELECT p.primary_key FROM p10_client_propre p TABLESAMPLE SYSTEM(1)
56
57DROP TABLE IF EXISTS temp_sales_sample;
58CREATE UNLOGGED TABLE temp_sales_sample AS
59	SELECT * FROM p10_vente_propre p
60	RIGHT JOIN temp_neolaneid_sample t ON t.primary_key = p.idclient;
61
62CREATE INDEX ON temp_sales_sample (idclient);
63CREATE INDEX ON temp_sales_sample (datetransaction);
64ANALYSE temp_sales_sample;
65
66-- pq y a t il des null ???? On les supprime
67DELETE FROM temp_sales_sample WHERE idclient is null;
68
69
70SELECT t.idclient
71	, sum(t.ca) 
72FROM temp_sales_sample t
73GROUP BY 1
74ORDER BY 2 DESC;
75
76-- Check example
77SELECT * FROM temp_sales_sample WHERE idclient = '51165675';
78
79SELECT date_trunc('month', datetransaction) as month_transac, SUM(ca) FROM temp_sales_sample WHERE idclient = '51165675'
80GROUP BY  month_transac
81ORDER BY month_transac ;
82
83-- TODO keep local curency name or drop local amount (ca_local)
84CREATE UNLOGGED TABLE IF NOT EXISTS temp_sales_sample_month_aggr AS
85	SELECT idclient
86		, date_trunc('month', datetransaction) as month_transac
87		, SUM(ca) as ca
88		, SUM(ca_local) as ca_local
89		, SUM(qte) as qte
90	FROM temp_sales_sample
91	GROUP BY 1, 2
92	ORDER BY 2;
93
94CREATE INDEX ON temp_sales_sample_month_aggr (idclient);
95CREATE INDEX ON temp_sales_sample_month_aggr (month_transac);
96ANALYZE temp_sales_sample_month_aggr;
97
98
99*/
100
101SELECT t1.idclient
102	, t1.month_transac
103	, t1.ca
104	, SUM(t2.ca) as ca_12dm 
105FROM temp_sales_sample_month_aggr t1
106LEFT JOIN temp_sales_sample_month_aggr t2 USING (idclient)
107WHERE t1.idclient = '51165675' AND
108t2.month_transac >= (t1.month_transac - interval '12 months') AND t2.month_transac < t1.month_transac 
109GROUP by t1.idclient
110	, t1.month_transac
111	, t1.ca
112ORDER BY 2
113LIMIT 200;SELECT *, pg_size_pretty(total_bytes) AS total
114    , pg_size_pretty(index_bytes) AS INDEX
115    , pg_size_pretty(toast_bytes) AS toast
116    , pg_size_pretty(table_bytes) AS TABLE
117  FROM (
118  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
119      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
120              , c.reltuples::bigint AS row_estimate
121              , pg_total_relation_size(c.oid) AS total_bytes
122              , pg_indexes_size(c.oid) AS index_bytes
123              , pg_total_relation_size(reltoastrelid) AS toast_bytes
124          FROM pg_class c
125          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
126          WHERE relkind = 'r'
127  ) a
128) a
129ORDER BY total_bytes DESC
130;