Rails сложный запрос для подсчета уникальных записей на основе таблицы истинности

Использование Рельсов. У меня есть следующий код: 



class TypeOfBlock < ActiveRecord::Base

  has_and_belongs_to_many :patients

end



class Patient < ActiveRecord::Base

  has_and_belongs_to_many :type_of_blocks, dependent: :destroy

end


С этими наборами таблиц: 



╔══════════════╗

║type_of_blocks║

╠══════╦═══════╣

║ id   ║ name  ║

╠══════╬═══════╣

║  1   ║ UP    ║

║  2   ║ LL    ║

║  3   ║ T     ║

╚══════╩═══════╝



╔═══════════════════════════════╗

║    patients_type_of_blocks    ║ 

╠══════════════════╦════════════╣

║ type_of_block_id ║ patient_id ║

╠══════════════════╬════════════╣

║                1 ║          1 ║

║                1 ║          2 ║

║                2 ║          2 ║

║                3 ║          3 ║

║                2 ║          4 ║

║                1 ║          5 ║

║                1 ║          6 ║

║                2 ║          6 ║

║                3 ║          6 ║

╚══════════════════╩════════════╝


Я хочу подсчитать количество уникальных пациентов в зависимости от типа комбинации блоков, вот ожидаемый результат: 



# Expected results (just like a truth table)

UP (patient with type_of_block_id 1 only) = 2 patient

UP + LL (patient with type_of_block_ids 1 and 2) = 1 patient

UP + T (patient with type_of_block_ids 1 and 3) = 0 patient

LL (patient with type_of_block_id 2 only) = 1 patient

LL + T (patient with type_of_block_ids 2 and 3) = 0 patient

T (patient with type_of_block_id 3 only) = 1 patient

UP + LL + T (patient with type_of_block_ids 1, 2 and 3) = 1 patient


Я попытался объединить таблицы, как показано ниже: 



up_ll =

  TypeOfBlock.

    joins("join patients_type_of_blocks on patients_type_of_blocks.type_of_block_id = type_of_blocks.id").

    where("patients_type_of_blocks.type_of_block_id = 1 and patients_type_of_blocks.type_of_block_id = 2").

    size


Но здесь слишком много сложностей, и число неверно. Я хотел попробовать raw SQL, но Rails 4 устарел и требует, чтобы я сделал ModelClass.find_by_sql.

Как я могу генерировать выше ожидаемое результаты?

678   3  

3 ответов:

Единственное решение, которое приходит мне на ум, - это использовать raw SQL и использовать group_concat функция, как показано здесь .

SQL нужен такой: 

SELECT
  combination,
  count(*) as cnt
FROM (
       SELECT
         ptb.patient_id,
         group_concat(tb.name ORDER BY tb.name) AS combination
       FROM type_of_blocks tb
       INNER JOIN patients_type_of_blocks ptb ON ptb.type_of_block_id = tb.id
       GROUP BY ptb.patient_id) patient_combinations
GROUP BY combination;
Внутренний выбор групп по пациентам и выбирает комбинации типов блоков, которые есть у каждого пациента. Внешний выбор затем просто подсчитывает пациентов в каждой комбинации.

Запрос возвращает следующее (см. скрипку SQL):

combination     cnt
LL              1
LL,T,UP         1
LL,UP           1
T               1
UP              2

Как вы можете видеть, запрос не возвращает нулевых отсчетов, это должно быть решено в коде ruby (возможно, инициализировать хэш со всеми комбинациями с нулями, а затем объединить с подсчетами запроса).

Чтобы интегрировать этот запрос в ruby, просто используйте метод find_by_sql на любой модели (и, например, преобразуйте результаты в хэш): 

sql = <<-EOF
        ...the query from above...
        EOF

TypeOfBlock.find_by_sql(sql).to_a.reduce({}) { |h, u| h[u.combination] = u.cnt; h }
# => { "LL" => 1, "LL,T,UP" => 1, "LL,UP" => 1, "T" => 1, "UP" => 2 }
5  
2017-05-23 14:59:35

Ответ Борама является правильным. Я просто хочу обратиться:

Как вы можете видеть, запрос не возвращает ноль отсчетов, это должно быть так. решается в коде ruby (возможно инициализировать хэш со всеми комбинациями с нулями, а затем объединить с количеством запросов).

Это может быть достигнуто с помощью чистого MySQL: 

SELECT sub.combination, COALESCE(cnt, 0) AS cnt
FROM (SELECT GROUP_CONCAT(Name ORDER BY Name SEPARATOR ' + ') AS combination
      FROM (SELECT p.Name, p.rn, LPAD(BIN(u.N + t.N * 10), size, '0') bitmap
            FROM (SELECT @rownum := @rownum + 1 rn, id, Name
                  FROM type_of_blocks, (SELECT @rownum := 0) r) p
            CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 
                    UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
                    UNION ALL SELECT 8 UNION ALL SELECT 9) u
             CROSS JOIN (SELECT 0 N UNION ALL SELECT 1 
                    UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
                    UNION ALL SELECT 8 UNION ALL SELECT 9) t
             CROSS JOIN (SELECT COUNT(*) AS size FROM type_of_blocks) o
             WHERE u.N + t.N * 10 < POW(2, size)
             ) b
       WHERE SUBSTRING(bitmap, rn, 1) = '1'
       GROUP BY bitmap
) AS sub
LEFT JOIN (
    SELECT combination, COUNT(*) AS cnt
    FROM (SELECT ptb.patient_id,
                GROUP_CONCAT(tb.name ORDER BY tb.name SEPARATOR ' + ') AS combination
          FROM type_of_blocks tb
          JOIN patients_type_of_blocks ptb 
            ON ptb.type_of_block_id = tb.id
          GROUP BY ptb.patient_id) patient_combinations
    GROUP BY combination   
) AS sub2
  ON sub.combination = sub2.combination
ORDER BY LENGTH(sub.combination), sub.combination;

SQLFiddleDemo

Вывод: 

╔══════════════╦═════╗
║ combination  ║ cnt ║
╠══════════════╬═════╣
║ T            ║   1 ║
║ LL           ║   1 ║
║ UP           ║   2 ║
║ LL + T       ║   0 ║
║ T + UP       ║   0 ║
║ LL + UP      ║   1 ║
║ LL + T + UP  ║   1 ║
╚══════════════╩═════╝

Как это работы:

  1. генерировать все возможные комбинации, используя метод, описанный Serpiton (с небольшими улучшениями)
  2. вычислить доступные комбинации
  3. объединить оба результата

Чтобы лучше понять, как это работает Postgresql версия генерации всех комбинаций: 

WITH all_combinations AS (
    SELECT string_agg(b.Name ,' + ' ORDER BY b.Name) AS combination
    FROM (SELECT p.Name, p.rn, RIGHT(o.n::bit(16)::text, size) AS bitmap
          FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id)::int AS  rn
                FROM type_of_blocks )AS p
          CROSS JOIN generate_series(1, 100000) AS o(n)     
          ,LATERAL(SELECT COUNT(*)::int AS size FROM type_of_blocks) AS s
          WHERE o.n < 2 ^ size
         ) b
    WHERE SUBSTRING(b.bitmap, b.rn, 1) = '1'
    GROUP BY b.bitmap
)
SELECT sub.combination, COALESCE(sub2.cnt, 0) AS cnt
FROM all_combinations sub
LEFT JOIN (SELECT combination, COUNT(*) AS cnt
           FROM (SELECT ptb.patient_id,
                 string_agg(tb.name,' + ' ORDER BY tb.name) AS combination
                 FROM type_of_blocks tb
                 JOIN patients_type_of_blocks ptb 
                   ON ptb.type_of_block_id = tb.id
                 GROUP BY ptb.patient_id) patient_combinations
           GROUP BY combination) AS sub2
  ON sub.combination = sub2.combination
ORDER BY LENGTH(sub.combination), sub.combination;

SqlFiddleDemo2

5  
2017-05-23 15:16:10

Вы также можете работать только с рельсами

Ожидается 

class PatientsTypeOfBlock < ActiveRecord::Base
  belongs_to :patient
  belongs_to :type_of_block
end

Запрос в качестве примера

PatientsTypeOfBlock.joins( :type_of_block, :patient ).where( "type_of_blocks.name = ?", "UP" ).count
0  
2016-04-04 17:23:11

Comments

    Ничего не найдено.
Click here to cancel reply.