[Postgres] Group and Aggregate Data in Postgres

最新推荐文章于 2024-08-26 11:47:42 发布

转载最新推荐文章于 2024-08-26 11:47:42 发布 · 50 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Answer1215/p/6532739.html

本文介绍如何使用PostgreSQL的CASE语句进行电影数据的类别转换，并通过GROUP BY进行数据聚合，以统计不同类别的电影数量。

How can we see a histogram of movies on IMDB with a particular rating? Or how much movies grossed at the box office each month? Or how many movies there are of each genre? These are examples of data aggregation questions, and this lesson will teach us how to answer them.

In the table we have 'action', 'animation'... 'short' categories. They all use 'true' of 'false'.

What if we want to use those by its categoreis not just ture of false?

We can use 'CASE' in Postgres.

SELECT 
CASE
  WHEN action=true THEN 'action'
  WHEN animation=true THEN 'animation'
  WHEN comedy=true THEN 'comedy'
  WHEN drama=true THEN 'drama'
  WHEN short=true THEN 'short'
  ELSE 'other'
END AS genre,
title
FROM movies
LIMIT 100

And now we want to get "how many movies for each category" from previous result.

What we can do is using "GROUP BY" and "WITH":

WITH genres AS(
    SELECT 
    CASE
      WHEN action=true THEN 'action'
      WHEN animation=true THEN 'animation'
      WHEN comedy=true THEN 'comedy'
      WHEN drama=true THEN 'drama'
      WHEN short=true THEN 'short'
      ELSE 'other'
    END AS genre,
    title
    FROM movies
    LIMIT 100
) 
SELECT genre,
COUNT(*)
FROM genres
GROUP BY genre;

转载于:https://www.cnblogs.com/Answer1215/p/6532739.html