MySQL: сопоставление/заполнение матрицы списком почтовых индексов и идентификаторов

Я пытаюсь заполнить таблицу tbl_matrix. Первая строка содержит уникальные идентификаторы. Первый столбец содержит уникальные почтовые индексы (5 целых чисел).

Другая таблица tbl_list содержит три столбца: «zip_code», «id», «вхождение» (около 300 000 строк).

Теперь я хочу обновить/заполнить tbl_matrix алгоритмом/циклом, который проверяет, содержит ли tbl_list комбинацию почтового индекса и идентификатора, и возвращает вхождение в tbl_matrix. В некоторых случаях список содержит две или более одинаковых комбинаций zip_code и ID, но с разным количеством вхождений. В этом случае вхождения должны суммироваться.

Я использую MySQL Workbench на компьютере с Windows, и у меня нет опыта работы с Perl/Python/PHP, поэтому было бы здорово решить проблему исключительно в MySQL.

В Excel я бы использовал функцию vlookup для каждого столбца в tbl_matrix, но до сих пор я не мог адаптировать другие подобные (My) примеры SQL из stackoverflow. Было бы здорово, если бы вы могли мне помочь (это проект колледжа). Большое спасибо, Ларс.


mysql dynamic-pivot
person LarsVegas    schedule 03.09.2015    source источник
comment
не понятно чего вы хотите. Должна ли tbl_matrix содержать все уникальные комбинации id, zip из tbl_list?   -  person splash58    schedule 03.09.2015
comment
Да, матрица должна быть заполнена суммой «вхождений» всех уникальных комбинаций ID и zip. Но tbl_matrix уже содержит все возможные идентификаторы (первая строка) и почтовые индексы (первый столбец). Остальная часть tbl_matrix пуста. Надеюсь, это ответило на ваш вопрос.   -  person LarsVegas    schedule 03.09.2015
comment
тогда матрица имеет поле для sum of 'occurrence' ?   -  person splash58    schedule 03.09.2015
comment
может быть, вы показываете структуру матрицы?   -  person splash58    schedule 03.09.2015

Ответы (2)


arrow_upward
0
arrow_downward

я думаю, что это может быть подходом к решению проблемы

create table thetable (id int, zip_code int,  occurrence int);
insert into thetable values (1,1,1), (2,1,2), (1,2,3), (1,2,4);

select id, 
       sum(if(zip_code=1, sum, 0)) zip1,
       sum(if(zip_code=2, sum, 0)) zip2
     from 
       (select id, zip_code, sum(occurrence) sum 
           from tbl_list  
         group by id, zip_code
       ) t
     group by id

результат

| id | zip1 | zip2 |
|----|------|------|
|  1 |    1 |    7 |
|  2 |    2 |    0 |

Демонстрация sqlfiddle

person splash58    schedule 03.09.2015
comment
ОП, вероятно, хочет динамические столбцы; создание того же SQL с использованием GROUP_CONCAT, что и в этом ответе, и выполнение динамического SQL. - person Zev Spitz; 03.09.2015

arrow_upward
0
arrow_downward

Если я правильно понимаю, то, что вы хотите сделать, это создать динамическую сводную таблицу с использованием MySQL (динамическую, потому что вы не хотите определять столбцы вручную, а скорее сгенерировать их из какого-либо другого источника, в данном случае уникальные почтовые индексы). См. здесь.

Также обратите внимание, что в Excel первая строка должна быть уникальными почтовыми индексами, потому что вы не можете изменить имена столбцов (A, B, C и т. д.). Однако в SQL имена столбцов могут и должны быть уникальными почтовыми индексами, поэтому нет необходимости в первой строке почтовых индексов.

person Zev Spitz    schedule 03.09.2015