CUBE 相当の SQL を生成するメソッド

0_to_cube.rb

# columns example
# [
#   ['hoge total', 'hoge'],
#   ['fuga total', 'fuga'],
#   ['piyo total', 'piyo'],
# ]
def cube_select_expressions(columns)
  [true, false].repeated_permutation(columns.size).map { |flags|
    flags.zip(columns).map { |flag, (total_label, column_name)|
      flag ? column_name : "'#{total_label}' AS #{column_name}"
    }.join(', ')
  }
end

columns = [
  ['hoge total', 'hoge'],
  ['fuga total', 'fuga'],
  ['piyo total', 'piyo'],
]

puts cube_select_expressions(columns).map { |select|
  <<~SQL
    SELECT #{select}
         , COUNT(DISTINCT moge) AS moge_count
      FROM hoge_fuga_piyo_moges
  GROUP BY 1, 2, 3
  SQL
}.join("\nUNION ALL \n\n")

1_result.sql

  SELECT hoge, fuga, piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT hoge, fuga, 'piyo total' AS piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT hoge, 'fuga total' AS fuga, piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT hoge, 'fuga total' AS fuga, 'piyo total' AS piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT 'hoge total' AS hoge, fuga, piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT 'hoge total' AS hoge, fuga, 'piyo total' AS piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT 'hoge total' AS hoge, 'fuga total' AS fuga, piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3

UNION ALL 

  SELECT 'hoge total' AS hoge, 'fuga total' AS fuga, 'piyo total' AS piyo
       , COUNT(DISTINCT moge) AS moge_count
    FROM hoge_fuga_piyo_moges
GROUP BY 1, 2, 3