{ "version": "https:\/\/jsonfeed.org\/version\/1", "title": "Блог об аналитике, визуализации данных, data science и BI, заметки с тегом: лайфхак", "home_page_url": "http:\/\/test.leftjoin.ru\/tags\/layfhak\/", "feed_url": "http:\/\/test.leftjoin.ru\/tags\/layfhak\/json\/", "icon": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg", "author": { "name": "Николай Валиотти", "url": "http:\/\/test.leftjoin.ru\/", "avatar": "http:\/\/test.leftjoin.ru\/user\/userpic@2x.jpg" }, "items": [ { "id": "89", "url": "http:\/\/test.leftjoin.ru\/all\/unpivot-with-cross-join\/", "title": "UNPIVOT данных с использованием CROSS JOIN", "content_html": "

Зачастую мы получаем данные в предагрегированном виде, когда каждая отдельная колонка является посчитанной метрикой. По аналогии мы получаем подобный результат, когда строим сводную таблицу в Excel и используем некоторое количество фактов для агрегации. Но что делать, если нам нужно произвести обратную операцию — Unpivot?<\/p>\n

Как поступить, если в датасете понадобилось трансформировать данные в реляционный вид? В Tableau есть фича Unpivot<\/a>, которая сделает всё сама: если датасет построен из файла, достаточно выделить нужные колонки и нажать на кнопку «Pivot». А в некоторых диалектах SQL, например, в Transact, уже есть встроенные функции<\/a>, которые тоже делают это сами.<\/p>\n

\n\"\"\n<\/div>\n

Но в случае, если датасет построен на Custom SQL Query из базы данных, у которой в арсенале отсутствуют встроенные функции для трансформации в сводную и обратно, необходим какой-то другой подход, и Tableau порекомендует для такой таблицы:<\/p>\n

\n\n\n\n\n
ID<\/b><\/td>\na<\/b><\/td>\nb<\/b><\/td>\nc<\/b><\/td>\n<\/tr>\n
1<\/td>\na1<\/td>\nb1<\/td>\nc1<\/td>\n<\/tr>\n
2<\/td>\na2<\/td>\nb2<\/td>\nc2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n

Воспользоваться таким стандартным универсальным, но не очень эффективным решением:<\/p>\n

select id, ‘a’ AS col, a AS value\r\nfrom yourtable\r\nunion all\r\nselect id, ‘b’ AS col, b AS value\r\nfrom yourtable\r\nunion all\r\nselect id, ‘c’ AS col, c AS value\r\nfrom yourtable<\/code><\/pre>
И в результате получить таблицу вида:<\/p>\n
\n\n\n\n\n\n\n\n\n
id<\/b><\/td>\ncol<\/b><\/td>\nvalue<\/b><\/td>\n<\/tr>\n
1<\/td>\na<\/td>\na1<\/td>\n<\/tr>\n
2<\/td>\na<\/td>\na2<\/td>\n<\/tr>\n
1<\/td>\nb<\/td>\nb1<\/td>\n<\/tr>\n
2<\/td>\nb<\/td>\nb2<\/td>\n<\/tr>\n
1<\/td>\nc<\/td>\nc1<\/td>\n<\/tr>\n
2<\/td>\nc<\/td>\nc2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n
Порой, когда мы работаем с физической таблицей и нам надо быстро получить результаты для двух-трех колонок, действительно, подобное решение можно быстро применить, не задумываясь. Однако в случае, когда вместо таблицы содержится, например, сложный подзапрос с несколькими джойнами и нужно сделать Pivot для 5+ колонок, подзапрос вызовется целых 5+ раз, согласитесь, не очень действенно считать одно и тоже неоднократно. Вместо этого можно воспользоваться рецептом с CROSS JOIN, найденным на просторах Stack Overflow:<\/p>\n
select t.id,\r\nc.col,\r\n    case c.col\r\n        when 'a' then a\r\n        when 'b' then b\r\n        when 'c' then c\r\n    end as data\r\nfrom yourtable t\r\ncross join\r\n(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre>
Разберём запрос подробнее. CROSS JOIN — перекрёстное соединение, декартово произведение, или, проще говоря, произведение всех строк со всеми. За ненадобностью в синтаксисе CROSS JOIN отсутствует ON — мы объединяем не по какому-то конкретному полю две таблицы, а сразу по всем существующим строкам.<\/p>\n
\n\"\"\n<\/div>\n
Сначала мы формируем таблицу со всеми колонками, предназначенными для преобразования в строки. В нашем случае это колонки a, b и c: поэтому мы сделали таблицу c, в которой будет колонка col со значениями a, b и c:<\/p>\n
(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre>
Выглядит она так:<\/p>\n
\n\n\n\n\n\n
col<\/b><\/td>\n<\/tr>\n
a<\/td>\n<\/tr>\n
b<\/td>\n<\/tr>\n
c<\/td>\n<\/tr>\n<\/table>\n<\/div>\n
Затем таблицы yourtable и c объединятся перекрестным соединением, а после мы возьмём поля id, col и в зависимости от того, как называется ячейка в col, подставим соответствующие данные в поле data.<\/p>\n
select t.id,\r\nc.col,\r\n    case c.col\r\n        when 'a' then a\r\n        when 'b' then b\r\n        when 'c' then c\r\n    end as value\r\nfrom yourtable t\r\ncross join\r\n(\r\n    select 'a' as col\r\n    union all select 'b'\r\n    union all select 'c'\r\n) c<\/code><\/pre>
В итоге получим ту же самую искомую таблицу, с которой уже можно удобно работать любым аналитическим инструментом:<\/p>\n
\n\n\n\n\n\n\n\n\n
id<\/b><\/td>\ncol<\/b><\/td>\nvalue<\/b><\/td>\n<\/tr>\n
1<\/td>\na<\/td>\na1<\/td>\n<\/tr>\n
2<\/td>\na<\/td>\na2<\/td>\n<\/tr>\n
1<\/td>\nb<\/td>\nb1<\/td>\n<\/tr>\n
2<\/td>\nb<\/td>\nb2<\/td>\n<\/tr>\n
1<\/td>\nc<\/td>\nc1<\/td>\n<\/tr>\n
2<\/td>\nc<\/td>\nc2<\/td>\n<\/tr>\n<\/table>\n<\/div>\n",
            "date_published": "2021-01-08T16:30:14+03:00",
            "date_modified": "2021-01-08T16:31:33+03:00",
            "image": "http:\/\/test.leftjoin.ru\/pictures\/qs_pivot_example.png",
            "_date_published_rfc2822": "Fri, 08 Jan 2021 16:30:14 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "89",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "http:\/\/test.leftjoin.ru\/pictures\/qs_pivot_example.png",
                    "http:\/\/test.leftjoin.ru\/pictures\/Background_2.png"
                ]
            }
        }
    ],
    "_e2_version": 3365,
    "_e2_ua_string": "E2 (v3365; Aegea)"
}