I have a database with about 2500 results from 2022. I'm trying to build queries that will show me the top industry and country results from each month throughout the entire year. The preferred output would include the top X (number) of each category. I'm fine with breaking this out into two queries, one for each category, if needed.

The table looks like this:

post_id int
post_date date
post_victim text
threat_group text
victim_industry text
victim_country text

The idea here is that I can use these queries to put together data to brief teammates or generate graphs for visualizing the resulting data.

Here's an example of what I'd like to output via a SQL query (shortened to just Jan-Apr, but the final result should cover the entire year):

I tried using crosstab and specifying the following as ct (...:

SELECT *
FROM crosstab(
  $$
  SELECT to_char(post_date, 'YYYY-MM') as month, victim_industry, count(*)
  FROM ransomware_posts
  WHERE post_date >= '2022-01-01' AND post_date < '2023-01-01'
  GROUP BY month, victim_industry
  ORDER BY month, count DESC
  $$
) as ct (month text, industry_1 text, count_1 int, industry_2 text, count_2 int)

I think the issue may have something to do with the fact that there are more than 2 results per month, so the output is receiving more results than it's expecting, but I don't know for sure as I've never built a functioning crosstab query.

Here's the error I receive:

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

CodePudding user response：

The error message comes from a gap between the structure resulting ffrom the internal query and the structure returned by the crosstab function.

This query should work :

SELECT *
FROM crosstab($$
  SELECT victim_industry, to_char(post_date, 'YYYY-MM') :: text as month, count(*) :: int
  FROM ransomware_posts
  WHERE post_date >= '2021-01-01' AND post_date < '2022-01-01'
  GROUP BY victim_industry, month
  ORDER BY victim_industry, month $$
) as ct (industry text, Jan int, Feb int, Mar int, Apr int, May int, Jun int, Jul int, Aug int, Sep int, Oct int, Nov int, Dec int)

UPDTATE

crosstab doesn't manage well the gaps if any.

There is another solution which relies on a composite type and the standard function jsonb_populate_record :

First creating a dedicated composite type :

CREATE TYPE months AS (jan int, feb int, mar int, apr int, jun int, jul int, aug int, sep int, oct int, nov int, dec int)

Then the following query provides the expected result :

SELECT r.victim_industry, (jsonb_populate_record(null :: months, jsonb_object_agg(lower(r.date), r.count))).*
  FROM 
     ( SELECT victim_industry, to_char(post_date, 'Mon') AS date, count(*) AS count
         FROM ransomware_posts
        WHERE post_date >= '2021-01-01' AND post_date < '2022-01-01'
        GROUP BY victim_industry, to_char(post_date, 'Mon')
    ) AS r
GROUP BY r.victim_industry

see dbfiddle