変態SQLの例
with login_count_partner as (
select
partner_id
, count(distinct created_time) as count_total_login
, count(distinct case when created_time >= current_date - interval'7 days' then created_time end) as
count_login_in_7
from partner_login
group by partner_id
), partner_movie_attach as (
select
mh.partner_id
, max(case when mmh.movie_id is not null then 1 else 0 end) as movie_attach_flg
from message_header mh
left join movie_message_header mmh on (mmh.message_header_id = mh.id)
group by mh.partner_id
), partner_sug_reg_count as (
select
a.partner_id
, count(distinct d.personal_code) as count_suggest
, count(distinct case when r.user_id is not null and r.deleted_time is null then d.personal_code end) as
count_register
from assignment a
join users d on (d.id = a.users_id and d.client_id = 1000)
left join m3_user m on (m.personal_code = d.personal_code)
left join registration r on (r.user_id = m.user_id and r.client_id = 1000)
where exists (select 1 from partner f where f.client_id = 1000 and f.id = a.partner_id)
and priority = 1
group by a.partner_id
)
select m.message_body_id
,to_char(m.send_time, 'YYYY-MM-DD') as send_date
,(select h.message_body_id from message_header h where h.id = m.parent_id) as parent_body_id
,g1.name as shiten
,g2.name as team
,to_char(m.send_time, 'YYYY-MM-DD') as person_answered_date
,'"'||d.workplace_name||'"' as workplace_name
,m.user_id as user_id
,d.personal_code as personal_person_cd
,d.name as person_name
,g3.name as tanto
,f.name as partner_name
,f.id as partner_id
,coalesce(a.movie_attach_flg, 0) as partner_movie_attach_flg
,coalesce(l.count_total_login, 0) as partner_login_count_total
,coalesce(l.count_login_in_7, 0) as partner_login_count_in_7
,coalesce(src.count_suggest, 0) as partner_count_suggest_person
,coalesce(src.count_register, 0) as partner_count_register_person
,case when (case when not ( 1=0 OR m.title like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押さ
れました%' OR m.title like '「もう少し詳しく」が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title
like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押されました%' OR m.title like '「もう少し詳しく」
が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title like '「なるほど」が押されました%' OR m.title
like '「頼りにしてます」が押されました%' OR m.title like '「ありがとう(K)」が押されました%' OR m.title like '「いいね
(K)」が押されました%' OR m.title like '「論文(K)」が押されました%' OR m.title like '「見たよ(K)」が押されました%' OR
m.title like '「次回に期待(K)」が押されました%' OR m.title like '「頼りにしてる(K)」が押されました%' ) then '"'||
regexp_replace( regexp_replace(regexp_replace(m.insertion_message, E'<[^>]*>', '', 'g'), E'[ ¥r¥n¥t]+', ' ', 'g'), '引
用本文.+', '') ||'"' end) is not null and not m.title like 'はい・いいえ回答%' then '1' else '0' end as
person_text_message_flg
,case when m.title like '「イイね！」が押されました%' then 1 else 0 end as stamp_mat
,case when m.title like '「すごくイイね！」が押されました%' then 1 else 0 end as stamp_good
,case when m.title like '「ぜひ会いたい！」が押されました%' then 1 else 0 end as stamp_visit
,case when m.title like '「見たよ」が押されました%' then 1 else 0 end as stamp_watch
,case when m.title like '「よくわからない」が押されました%' then 1 else 0 end as stamp_pre
,case when m.title like '「残念！」が押されました%' then 1 else 0 end as stamp_for
,case when m.title like '「もう少し詳しく」が押されました%' then 1 else 0 end as stamp_detail
,case when m.title like '「参考になったよ」が押されました%' then 1 else 0 end as stamp_helpful
,case when m.title like '「試してみようかな」が押されました%' then 1 else 0 end as stamp_try
,case when m.title like '「試してみたよ」が押されました%' then 1 else 0 end as stamp_new
,case when m.title like 'はい・いいえ回答%' then '1' else '0' end as yes_no
,case when m.title like '%が押されました%' then 1 else 0 end as stamp_all
,to_char(res.send_time, 'YYYY-MM-DD') as reply_date
from partner f
left join (
select
msg.user_id
, msg.personal_code
, msg.message_body_id
, msg.send_time
, msg.parent_id
, msg.title
from message msg
where msg.client_id = 1000
and msg.message_type = 'USER_TO_PARTNER'
and not exists (select 1 from test_users t where msg.user_id = t.user_id)
and msg.send_time is not null
and msg.send_time < current_date
) m on m.partner_id = f.id
left join m3_user m3 on m.user_id = m3.user_id
left join users d on m3.personal_code = d.personal_code and d.client_id = 1000
left join login_count_partner l on l.partner_id = f.id
left join partner_movie_attach a on (a.partner_id = f.id)
left join partner_sug_reg_count src on (src.partner_id = f.id)
left join message res on m.message_header_id = res.parent_id
left join partner_group g1 on f.group1 = g1.id
left join partner_group g2 on f.group2 = g2.id
left join partner_group g3 on f.group3 = g3.id
where f.client_id = 1000
suzukihoge
jmatsu
manfredsteyer
heyitsmohit
mkeeda
gmvalentino8
vrallev
aethiopicuschan
daipresents
wadayusuke
uhooi
schroneko
moore
quramy
eitanlees
andyhume
sferik
hursman
yeseniaperezcruz
trishagee
keavy
3n
addyosmani
kevinliebholz