Блог

У меня есть этот вызов sql, который дает мне все необходимые значения, когда выполняются все условия WHERE. Однако я также хотел бы получить значение, точно такое же, как entrys_count , за исключением случаев, когда это конкретное условие не выполняется (найдено в предложении where — AND location_event_segments.exit = :cancelled

Полный код вставлен ниже:

 def query
  <<~SQL
    SELECT location_id,
      location_name,
      SUM(entrys_count)::INT AS entrys_count,
      CASE WHEN location_id IS NULL THEN NULL
        ELSE SUM(total_discussions)::INT
      END AS discussions_per_attendee,
      SUM(entrys_count * total_discussions)::INT AS total_discussions,
      ROUND(SUM(entrys_count * total_discussions)::NUMERIC / SUM(entrys_count), 1) AS average_discussions
    FROM (
      SELECT location_id,
        location_name,
        COUNT(location_event_id) AS entrys_count,
        total_discussions
        FROM (
          SELECT locations.id AS location_id,
            locations.name AS location_name,
            location_events.id AS location_event_id,
            #{discussions_sql} AS total_discussions
          FROM location_events
            JOIN locations
              ON locations.id = location_events.location_id
            JOIN location_event_segments
              ON location_events.id = location_event_segments.location_event_id
            JOIN events
              ON events.id = location_events.event_id
          WHERE location_event_segments.segment_id = :exit
            AND location_event_segments.exit = :cancelled
          GROUP BY locations.id, locations.name, location_events.id
        ) discussions
      GROUP BY location_id, location_name, total_discussions
    ) breakouts_per_discussions_count
    GROUP BY
      GROUPING SETS (
        (location_id, location_name, entrys_count, total_discussions),
        ()
      )
  SQL
end
  

Я не смог заставить это работать, не говоря уже о том, чтобы найти самый чистый способ сделать это. Любая помощь будет с благодарностью!

Вы можете использовать case when :

 def query
  <<~SQL
    SELECT location_id,
      location_name,
      SUM(entrys_count)::INT AS entrys_count,
      SUM(canceled_entrys_count)::INT AS canceled_entrys_count,
      CASE WHEN location_id IS NULL THEN NULL
        ELSE SUM(total_discussions)::INT
      END AS discussions_per_attendee,
      SUM(entrys_count * total_discussions)::INT AS total_discussions,
      ROUND(SUM(entrys_count * total_discussions)::NUMERIC / SUM(entrys_count), 1) AS average_discussions
    FROM (
      SELECT location_id,
        location_name,
        COUNT(location_event_id) AS entrys_count,
        COUNT(cancelled_location_event_id) AS canceled_entrys_count,
        total_discussions
        FROM (
          SELECT locations.id AS location_id,
            locations.name AS location_name,
            case when location_event_segments.exit = :cancelled then location_events.id else null end AS location_event_id,
            case when location_event_segments.exit <> :cancelled then location_events.id else null end AS cancelled_location_event_id,
            #{discussions_sql} AS total_discussions
          FROM location_events
            JOIN locations
              ON locations.id = location_events.location_id
            JOIN location_event_segments
              ON location_events.id = location_event_segments.location_event_id
            JOIN events
              ON events.id = location_events.event_id
          WHERE location_event_segments.segment_id = :exit
          GROUP BY locations.id, locations.name, case when location_event_segments.exit = :cancelled then location_events.id else null end,
             case when location_event_segments.exit <> :cancelled then location_events.id else null end
        ) discussions
      GROUP BY location_id, location_name, total_discussions
    ) breakouts_per_discussions_count
    GROUP BY
      GROUPING SETS (
        (location_id, location_name, entrys_count, total_discussions),
        ()
      )
  SQL
end