WordPressのテーマCocoonは閲覧回数をカウントする機能を持っています。
これをMySQLで操作して閲覧回数の多い記事を抽出してみました。
全期間の閲覧回数が多い投稿
SELECT wp_cocoon_accesses.post_id
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
一週間以内の閲覧回数が多い投稿
SELECT wp_cocoon_accesses.post_id
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
WHERE wp_cocoon_accesses.DATE >= (NOW() - INTERVAL 7 DAY)
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
WHERE wp_cocoon_accesses.DATE >= (NOW() - INTERVAL 7 DAY)
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
アイキャッチがない投稿
アイキャッチがセットされていない投稿を抽出します。
SELECT wp_cocoon_accesses.post_id
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
LEFT JOIN (
SELECT *
FROM `wp_postmeta`
WHERE meta_key LIKE '_thumbnail_id'
) postmeta ON wp_posts.id = postmeta.post_id
WHERE postmeta.meta_id IS NULL
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
,wp_posts.post_title
,sum(wp_cocoon_accesses.count) AS sum_count
FROM `wp_cocoon_accesses`
INNER JOIN `wp_posts` ON wp_cocoon_accesses.post_id = wp_posts.id
LEFT JOIN (
SELECT *
FROM `wp_postmeta`
WHERE meta_key LIKE '_thumbnail_id'
) postmeta ON wp_posts.id = postmeta.post_id
WHERE postmeta.meta_id IS NULL
GROUP BY wp_cocoon_accesses.post_id
ORDER BY sum_count DESC;
コメント