Страницы: 1
RSS
Поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
 
Всем привет !
Задача очень банальна и проста.....нужно узнать, в каком магазине есть товар.
Данные представлены в видео Код артикула | номер магазина , + есть список всех магазинов.

Мое решение было таковым - соединить артикул и код магазина и ВПРить. Все работает, но.. задача осложняется тем, что в данных более 10.000.000 артикулов ( 10 файлов xls по миллиону строк )

Уверен, что есть какое-то простое решение на базе Access , но для меня это дремуч лес.
Во вложении файлик пример.
Спасибо всем, кто потратит время.  
Изменено: Alex.Mari - 22.03.2019 22:19:08
 
Можно подключиться к этим 10файлам через PQ и сделать что-то подобное

для модераторов: поиск необходимых данных в огромном массиве и отображение результата в виде таблицы
Изменено: Dark1589 - 22.03.2019 16:02:53
 
Имхо, тут PQ не нужен.. задача элементарно решается сводной таблицей.
 
Alexey_Spb, по 10 млн строк?
 
Stics, Alexey_Spb, Давайте на примере 1 файла в 1 млн строк.  
 
Alexey_Spb, можно, но фильтровать артикулы лучше всё-равно заранее
 
Цитата
Stics написал:
Alexey_Spb , по 10 млн строк?

Пришлите пример с 10 млн. строк на a l е x е y _ b h (собако) майл.руъ
Изменено: Alexey_Spb - 22.03.2019 16:21:27
 
Цитата
Dark1589 написал:
но фильтровать артикулы лучше всё-равно заранее

В данном случае не думаю что PQ будет иметь преимущества по скорости перед сводной. Так же считаю что вариант Dark1589 не самый оптимальный с точки зрения скорости.
Изменено: Alexey_Spb - 22.03.2019 16:23:15
 
Alexey_Spb, Алексей, пришлите Ваше решение на основе вложенного мной файла в первом сообщении.
Спасибо.
 
хотел поплеваться, по поводу предложения сводной, не буду :)
я за PQ
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Alexey_Spb, потом напишите через что сведёте 10 файлов в сводную
 
Dark1589, Мне хотя бы сделать в одном.. я повторю действие во других 10 и уже после этого сведу данные.

Не нужно усложнять.  
 
Предполагаю что это один из самых быстрых способов.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Группируем таблицу по коду товара
    Grp = Table.Group
           (
            // На таких объемах данных буферизация - наше все имхо
            Table.Buffer(Source), 
            {"Код товара"}, 
            {
             // Добавляя столбец Data, в ячейках которого будут содержаться таблицы
             // с колонками, соответствующими именам магазинов где есть данный товар 
             // и единственной строкой, содержащей во всех колонках "x".
             {
 
              "Data", 
              each Table.Buffer
                    (
                     // Нестандартное использование Pivot - вместо агрегации данных 
                     // ставим значение "x" во все колонки (имена магазинов, в которых
                     // есть группируемый товар.
                     Table.Pivot(_, List.Distinct([Магазин]), "Магазин", "Код товара", each "x")
                    )
             }
            }
           ),

    // Это получение уникального сортированного списка всех магазинов для использования в качестве 
    // имен колонок в готовой таблице.
    // Я создаю его, преобразовывая столбец Data, каждая ячейка которого является таблицей,
    // в столбец, содержащий список имен колонок таблицы в ячейке. А дальше путем слияния
    // списка списков (мутировавший столбец [Data] я получаю все возможные варианты имен магазинов.
    List = List.Sort
            (
             List.Distinct
              (

               // Как вариант - Вместо List.Combine использовать List.Buffer(Source[Магазин]).   
               // То есть тупо вычислить уникальные комбинации из колонки Магазин исходной таблицы
               // Вполне может быть быстрее..

               List.Combine
                (
                 Table.TransformColumns(Grp, {{"Data", each Table.ColumnNames(_)}})[Data]
                )

              ), 
             Order.Ascending
            ),

    // А дальше просто разворачиваем столбец Data по полному списку магазинов.
    // В тех строках, где встречаются имена колонок, которые есть в в List,
    // выставляется значение "x", которое там хранится. Если нет (то есть товара
    // не было в магазине - то null)
    Expanded = Table.ExpandTableColumn(Grp, "Data", List)

in
    // В итоге получаем результат.
    Expanded

   
Изменено: Alexey_Spb - 22.03.2019 17:17:09
 
Оо….ну да, красиво...
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Проверил на такой таблице из миллиона строк. Выполнение основного запроса заняло 50 секунд.
Код
let
    Source = List.Transform({0..10}, each "Магазин " & Text.From(_)),
    Max = List.Count(Source) - 1,
    Tt = Table.FromRows(List.Transform(List.Random(1000000), each {Number.Round(_, 3) * 10000000, Source{Number.Round(Number.RandomBetween(0, Max), 0)}}), {"Код товара", "Магазин"})
in
   Tt

Сводная на миллионе строк сводная обновилась за 10 секунд :)  Ну очевидно же что схожие операции сводная выполнит быстрее.

Правда у сводной будет две проблемы - похабный вид данных (в ячейках суммы). Наверное, решается через Custom-столбец, но не проверял сколько времени в этом случае займет обновление. Ну и второе - из 10 файлов данные тянуть.
Изменено: Alexey_Spb - 22.03.2019 17:57:56
 
Alexey_Spb, Алексей, добрый день.
В Ваш файл внес свои данные.. запрос больше 15 -ти минут обрабатывается ( 100,000 +/- строк ) .. пока ничего не выходит

Файл не могу вложить... недопустимый размер
Положил тут https://dropmefiles.com/pVC9a

Возможно Вы придумаете какое-то решение для ускорения процесса.
Спасибо.  
 
Доброе время суток
Цитата
Alexey_Spb написал:
Предполагаю что это один из самых быстрых способов.
Алексей, а чем плох обычный Table.Pivot?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "marker", each "x"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Магазин", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Магазин]), "Магазин", "marker")
in
    #"Pivoted Column"
 
Андрей VG, Добрый день. Спасибо,  это работает. Подскажите, пожалуйста, есть ли возможность добавить в расчеты третий столбец ?
Файл прикрепил с вашим Table.Pivot

В файле есть визуальное отображение что мне нужно.
Спасибо, если поможете.
 
Цитата
Alex.Mari написал:
добавить в расчеты третий столбец ?
Если вы смотрели код, то третий столбец там уже фигурировал ;) , так что не проблема
Код
let
    listToText = (items) => Text.Combine(items, "/"),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Магазин", Order.Ascending}, {"Столбец1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Магазин]), "Магазин", "Столбец1", listToText)
in
    #"Pivoted Column"
 
Андрей VG,Спасибо. Это полное решение моего вопроса. Прошу в личку прислать куда отблагодарить.  
Страницы: 1
Наверх