各サークルの代表作？ボイス作品編の実行結果
SQLの説明
サークルのお気に入り数が多め(ただし新しめのものを優先)の作品を並べてみました
検索結果
実行したSQL
select 発売日,売上数／気入数,率,測定日,セール価格,サークル名,画像
from
(select
'<A href="https://www.dmm.co.jp/dc/doujin/-/schedule/=/media=voice/release_date='||delivery_start_date
||'/sort=bookmark_desc/" target="_blank">'||delivery_start_date||'</A>'
as 発売日 ,
sales||'<br>'||
number_of_favorites
||case when delivery_start_date=create_date
then'+' else '' end
as 売上数／気入数,
number_of_favorites*100/max 率,
rank()over(PARTITION BY maker_id
order by -number_of_favorites/0.01/max),
create_date as 測定日,
case when salerate>50 then
'<b>'||saleprice||'</b>' else
saleprice||'' end as セール価格,
'<A href="https://www.dmm.co.jp/dc/doujin/-/list/=/article=maker/exclude=ai.none/id=
'||s.maker_id||'/limit=30/sort=bookmark_desc/
" target="_blank">'||maker_name||'</A>'
as サークル名,
case when max= number_of_favorites
then
'<A href="https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=
'||cid||'/" target="_blank"><img src="'||img||'"></a>'
when max<= number_of_favorites*5
then
'<A href="https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=
'||cid||'/" target="_blank"><img src="'||img||'"height= "200"></a>'
else
'<A href="https://www.dmm.co.jp/dc/doujin/-/detail/=/cid=
'||cid||'/" target="_blank">'||title||'</A>'
end as 画像
from
(select
min(case when campaign_due_date>=current_date then price else null end) as saleprice ,
max(case when campaign_due_date>=current_date then discount_rate else null end) as salerate ,
cid,max(title) as title,max(maker_id) as maker_id,max(maker_name)as maker_name,max(img) as img,max(number_of_sales) as sales,max(campaign_due_date) as campaign,
delivery_start_date,max(number_of_favorites) as number_of_favorites,max(create_date) as create_date
from fanza_doujin_sales s
where s.genre like 'ボイス%' and number_of_favorites
>=200
group by cid, delivery_start_date)s,
(SELECT a.sellday,max(b.count)
FROM
(select
distinct
delivery_start_date
as sellday
from fanza_doujin_sales
where
number_of_favorites
>=200 and genre like 'ボイス%'
)a,
(select delivery_start_date
as sellday
,number_of_favorites
as count ,
rank()over(PARTITION BY delivery_start_date
order by -number_of_favorites)
from fanza_doujin_sales
where
number_of_favorites
>=200 and genre like 'ボイス%'
)b
where a.sellday<=b.sellday and rank=1
group by a.sellday)m
where sellday=delivery_start_date
and
max<=number_of_favorites*10
order by
delivery_start_date desc,number_of_favorites desc)t
where rank=1