sql

SQL

-- 채널 목록과 마지막 업데이트
select 
  max(y.publishedAt) publishedAt,
  count(y.id) cnt,
  c.*
from channels c 
join youtubes y on c.id = y.ChannelId
group by c.id
order by publishedAt desc
;

-- 시간별 publish 통계
select
	hr,
	count(hr)
from
	(
	select
		y.publishedAt,
		c.category,
		substr(y.publishedAt, 12, 2) hr
	from
		youtubes y
	join channels c on
		y.ChannelId = c.id
	where
		c.lang = 'ko'
) a
group by
	hr
;

select
	y.publishedAt
from
	youtubes y
join channels c on
	y.ChannelId = c.id
where
	c.customUrl = '@kenuheo';

select
	datediff('2024-04-01 10:53:44.000' - '2024-03-30 23:59:19.000');
-- https://youtu.be/upSRj9Mxc7g

select
	*
from
	youtubes y
where
	videoId in ('Eye0JiYU9kk',
'vmDusR6S9uM',
'mfSGybMSAQU',
'o5YTYaYSHXc',
'J4w3ZIBp6Ss',
'30nb6QAGVGc',
'tkSng25jnBQ',
'HEqpuHF1wZs'
);

delete
from
	youtubes
where
	videoId in ('Eye0JiYU9kk',
'vmDusR6S9uM',
'mfSGybMSAQU',
'o5YTYaYSHXc',
'J4w3ZIBp6Ss',
'30nb6QAGVGc',
'tkSng25jnBQ',
'HEqpuHF1wZs'
);

select
	c.id,
	c.channelId,
	max(publishedAt) mp,
	c.title
from
	youtubes y
join channels c on
	y.ChannelId = c.id
group by
	ChannelId
order by
	mp ;

select
	*
from
	channels c
where
	c.category = 'kpop';

select
	*
from
	youtubes y
join channels c on
	c.id = y.ChannelId
where
	c.category = 'kpop';

select
	*
from
	channels c
where
	title like '%판교%';

select
	c.lang,
	c.category,
	y.title,
	y.publishedAt
from
	youtubes y
join channels c on
	c.id = y.ChannelId
order by
	y.publishedAt desc;

select
	c.lang,
	c.category,
	count(*)
from
	youtubes y
join channels c on
	c.id = y.ChannelId
group by
	c.lang,
	c.category
order by
	y.publishedAt desc;

select
	*
from
	(
	select
		c.lang,
		c.category,
		y.title,
		y.publishedAt
	from
		youtubes y
	join channels c on
		c.id = y.ChannelId
	where
		c.lang = 'ko'
		and c.category = 'dev'
	order by
		y.publishedAt desc
	limit 0,
	5
) a
union all
select
	*
from
	(
	select
		c.lang,
		c.category,
		y.title,
		y.publishedAt
	from
		youtubes y
	join channels c on
		c.id = y.ChannelId
	where
		c.lang = 'en'
		and c.category = 'dev'
	order by
		y.publishedAt desc
	limit 0,
	5
) a
union all
select
	*
from
	(
	select
		c.lang,
		c.category,
		y.title,
		y.publishedAt
	from
		youtubes y
	join channels c on
		c.id = y.ChannelId
	where
		c.lang = 'ko'
		and c.category = 'food'
	order by
		y.publishedAt desc
	limit 0,
	5
) b
union all
select
	*
from
	(
	select
		c.lang,
		c.category,
		y.title,
		y.publishedAt
	from
		youtubes y
	join channels c on
		c.id = y.ChannelId
	where
		c.lang = 'ko'
		and c.category = 'drama'
	order by
		y.publishedAt desc
	limit 0,
	5
) b;
-- 카테고리별 최신 5개씩
select
	*
from
	(
	select
		c.lang,
		c.category,
		y.title,
		y.publishedAt,
		row_number() over (partition by c.lang,
		c.category
	order by
		y.publishedAt desc) as rn
	from
		youtubes y
	join channels c on
		c.id = y.ChannelId
) a
where
	a.rn <= 5
;