SQL tips

1. postgresql.sql

to_char(to_timestamp(dtime), 'YYYY.MM.DD' ) as mydate

to_char((avg(dtime-click_time)::int || ' second')::interval, 'HH24:MI:SS') as delta
 
substring(referer, 'utm_source=([^&]*)') as site

round((100*count(1)::real/sum(count(1)) OVER (PARTITION BY User))::numeric, 2) as percent

ip2int('185.3.35.151') as ipint

ip::inet + '0.0.0.0' as ip

array_to_string(array_agg(a.alias), E', ') as aliases

date_part('epoch',to_date('2015.05.13', 'YYYY.MM.DD')) 

pg_dump -U username -h hostname -O <no-owner> <database> > <sqlfile>
mysqldump -u root -p demodb > dbbackup.sql

//begining of the day, mysql
SELECT UNIX_TIMESTAMP(CURRENT_DATE())
//begining of the day, postgresql
SELECT extract('epoch' from CURRENT_DATE)

2. geo.sql

SELECT 
  CASE 
    WHEN g.geo_id = 1 OR g.parent_geo_id = 1 OR g2.parent_geo_id = 1 THEN 'UA'
    WHEN g.geo_id = 2 OR g.parent_geo_id = 2 OR g2.parent_geo_id = 2 THEN 'RU'
    ELSE 'OTH'
  END as geo_my, count(1) as clicks FROM tbl_detail_in_stat s
  JOIN tbl_cfg_aliases a ON a.id = s.alias_id
  JOIN tbl_cfg_geo_new g USING (geo_id)
  JOIN tbl_cfg_geo_new g2 ON g.parent_geo_id = g2.geo_id
WHERE utime between 1427835600 and 1427922000 AND a.src_id = 303417
GROUP BY geo_my
ORDER BY clicks DESC


ext:
WHEN g.geo_id = 30 OR g.parent_geo_id = 30 OR g2.parent_geo_id = 30 THEN 'KZ'
WHEN g.geo_id = 3 OR g.parent_geo_id = 3 OR g2.parent_geo_id = 3 THEN 'BY'
WHEN g.geo_id = 31 OR g.parent_geo_id = 31 OR g2.parent_geo_id = 31 THEN 'US'
WHEN g.geo_id = 38 OR g.parent_geo_id = 38 OR g2.parent_geo_id = 38 THEN 'DE'
WHEN g.geo_id = 1549 OR g.parent_geo_id = 1549 OR g2.parent_geo_id = 1549 THEN 'EU'
WHEN g.geo_id = 40 OR g.parent_geo_id = 40 OR g2.parent_geo_id = 40 THEN 'IL'
WHEN g.geo_id = 45 OR g.parent_geo_id = 45 OR g2.parent_geo_id = 45 THEN 'LV'
WHEN g.geo_id = 47 OR g.parent_geo_id = 47 OR g2.parent_geo_id = 47 THEN 'MD'
WHEN g.geo_id = 51 OR g.parent_geo_id = 51 OR g2.parent_geo_id = 51 THEN 'UZ'
WHEN g.geo_id = 1560 OR g.parent_geo_id = 1560 OR g2.parent_geo_id = 1560 THEN 'GB'
WHEN g.geo_id = 39 OR g.parent_geo_id = 39 OR g2.parent_geo_id = 39 THEN 'GE'