Построение таблицы на основе SQL-запроса к источнику данных

Следуя данной инструкции, вы построите в Editor таблицу на основе SQL-запроса к источнику данных. Для удобства в качестве источника данных мы будем использовать подключение и датасет из развернутого демо-воркбука.

Перед началом работы

Чтобы начать работать с DataLens, воспользуйтесь инструкцией по развертыванию.

Разверните демо-воркбук

  1. Сохраните из DataLens Gallery работу с названием DataLens Demo Dashboard:

    1. В DataLens Gallery откройте карточку работы.
    2. Нажмите Использовать → Скачать. Воркбук будет скачан в виде файла в формате JSON.
    3. На странице коллекций и воркбуков нажмите Создать → Воркбук.
    4. В открывшемся окне нажмите Выбрать файл и загрузите JSON-файл с конфигурацией демо-воркбука. При необходимости скорректируйте название и описание и нажмите Сохранить.

  2. Перейдите в созданный воркбук и на вкладке Подключения найдите подключение с названием 00: Demo Dashboard. Connection.

  3. Скопируйте идентификатор подключения: рядом с ним нажмите на → Копировать ID. Идентификатор будет скопирован в буфер обмена.

Создайте чарт в Editor

  1. В воркбуке в правом верхнем углу нажмите Создать → Чарт в Editor. На открывшейся странице выберите тип визуализации Таблица.

  2. Свяжите чарт с подключением: для этого перейдите на вкладку Meta и добавьте ID подключения в links:

    {
            "links": {
                "demoConnection": "<id_подключения>"
            }
        }

    Где:

    • <id_подключения> — идентификатор подключения, скопированный на предыдущем шаге.
    • demoConnection — произвольное имя-алиас, которое вы присваиваете подключению, с помощью которого запрашиваете данные для чарта из источника.

    Примечание

    Вкладка Meta нужна для описания служебной информации о списке связанных сущностей. Эта информация используется для определения, с какими подключениями и датасетами связан чарт, а также для диалога связанных объектов, при копировании воркбука и при публикации в Public.

  3. Получите данные из источника: для этого откройте вкладку Source и укажите:

    module.exports = {
            'salesSourceData': {
                qlConnectionId: Editor.getId('demoConnection'),
                data: {
                    sql_query: `
                    SELECT
                        t1."PaymentType" AS PaymentType,
                        toYear(toDate(t1."OrderDatetime")) AS OrderYear,
                        toStartOfInterval(toDate(toDate(t1."OrderDatetime")), interval 1 month) AS OrderMonth,
                        sum(toFloat64(t1."Price")) AS PriceSum
                    FROM samples."MS_SalesFacts_up" AS t1
                    GROUP BY PaymentType, OrderYear, OrderMonth
                    LIMIT 1000001`,
                },
            },
        };

    salesSourceData — произвольное имя-алиас, которое вы присваиваете объекту с запрошенными данными чарта, которое используется для доступа на вкладке Prepare.

    В значении поля sql_query описывается запрос к источнику данных подключения.

  4. Очистите содержимое вкладок Params и Config: они содержат шаблон, не относящийся к нашему примеру.

  5. На вкладке Prepare сформируйте таблицу:

    // Вспомогательная функция для подготовки полученных из источника данных в более удобном виде
        function getPreparedLoadedData(loadedData, sourceName) {
            const sourceData = loadedData[sourceName];
            const columnNames = sourceData.filter(item => item.event === 'metadata').map(item => item.data.names)?.[0] || [];
            const res = [];
            sourceData.filter(item => item.event === 'row').map(item => item.data).forEach(rowItem => {
                const rowHash = rowItem.reduce((acc, field, index) => {
                    acc[columnNames[index]] = field;
                    return acc;
                }, {});
                res.push(rowHash);
            });
            return res;
        }
        
        // Вспомогательная функция для группировки данных по заданному имени поля 
        function groupBy(arr, field) {
            return arr.reduce((acc, item) => {
                const key = item[field];
                if (!acc[key]) {
                    acc[key] = [];
                }
                acc[key].push(item);
                
                return acc;
            }, {});
        }
        
        // Преобразованные данные из источника в удобном формате
        const data = getPreparedLoadedData(Editor.getLoadedData(), 'salesSourceData');
        
        // Массив с уникальными значениями из поля "OrderYear", отсортированное по возрастанию чисел
        const years = Array.from(new Set(data.map(d => String(d['OrderYear'])))).sort();
        
        // Общие стили для ячеек-заголовков таблицы
        const headStyles = {background: 'var(--g-color-base-misc-light)', verticalAlign: 'middle'};
        
        // Конфигурация ячеек-заголовков таблицы
        const head = [
            {
                name: 'Тип оплаты',
                formattedName: Editor.generateHtml({
                    tag: 'span',
                    content: [
                        {tag: 'span', content: 'Тип оплаты'},
        				// всплывающая подсказка для заголовка ячейки
                        {
                            tag: 'dl-tooltip',
                            content: ' ℹ',
                            style: {
                                display: 'inline-block',
                                margin: '0px 0px 0px 4px',
                                'line-height': '12px',
                                'text-align': 'center',
                                width: '16px',
                                height: '16px',
                                border: '1px solid #ccc',
                                'border-radius': '50%',
                            },
                            attributes : {
                                'data-tooltip-content': {
                                    tag: 'i',
                                    content: 'Tooltip content',
                                },
                            },
                        }
                    ],
                }),
                css: headStyles,
                pinned: true,
            },
            // Формируем колонки по полученному выше массиву со значениями из поля "OrderYear"
            ...years.map(year => ({
                name: year,
                css: headStyles,
            })),
            {
                name: 'Продажи, все года',
                css: headStyles,
            },
        ];
        
        // Вспомогательная функция для отрисовки линии чарта
        function createChart(chartData) {
            const chartWidth = 80;
            const chartHeight = 40;
        
            // Вычисляем минимальное и максимальное значение координат
            const minX = Math.min(...chartData.map(d => d.x));
            const maxX = Math.max(...chartData.map(d => d.x));
            const minY = Math.min(...chartData.map(d => d.y));
            const maxY = Math.max(...chartData.map(d => d.y));
        
            // Вычисляем координаты в зависимости от размеров контейнера чарта (chartWidth, chartHeight)
            const coords = chartData.sort((d1, d2) => d1.x - d2.x).map(d => ([
                (d.x - minX) / (maxX - minX) * chartWidth, 
                (d.y - minY) / (maxY - minY) * chartHeight,
            ]));
            // Создаем путь для svg-линии по сформированным выше координатам
            let d = "";
            coords.forEach((_, x) => {
                d += d === "" ? "M" : " L";
                d += `${coords[x][0]} ${coords[x][1]}`;
            });
            // Создаем svg с цветом линии var(--g-color-base-brand) и толщиной 2px
            return `
                <svg width="${chartWidth}" height="${chartHeight}">
                    <path 
                        d="${d}" 
                        style="fill: none; stroke: var(--g-color-base-brand); stroke-width: 2;"
                    />
                </svg>`;
        }
        
        const rows = [];
        
        // Вспомогательная функция форматирования чисел
        const formatSalesValue = new Intl.NumberFormat('ru-RU').format;
        const postfix = ', ₽';
        
        // Сгруппированные строки по полю "PaymentType"
        const groupedData = groupBy(data, 'PaymentType');
        
        // Формируем и наполняем строки таблицы для каждого сгруппированного типа оплаты (на основе значения поля PaymentType)
        Object.entries(groupedData).forEach(([key, items]) => {
            // Сгруппированные строки по полю "OrderYear"
            const salesByYears = groupBy(items, 'OrderYear');
            // Вычисляем сумму по полю "PriceSum" по всем годам (сформированных на основе OrderYear)
            const totalSales = items.reduce((sum, d) => sum + d['PriceSum'], 0);
            rows.push({
                cells: [
                    {
                        value: key,
                    },
                    // Формируем колонки по подготовленным ранее значениям "OrderYear"
                    ...years.map(year => {
                        const salesByYear = salesByYears[year] ?? [];
                        const yearSales = salesByYear.map(d => ({
                            x: new Date(d['OrderMonth']).getTime(), 
                            y: d['PriceSum'],
                        }));
                        const maxSales = Math.max(...salesByYear.map(d => d['PriceSum']));
                        const minSales = Math.min(...salesByYear.map(d => d['PriceSum']));
        
                        return {
                            value: maxSales, 
                            formattedValue: Editor.generateHtml(`
                                <div>
                                    ${createChart(yearSales)}
                                    <div style="margin-top: 8px;">Min: <b>${formatSalesValue(minSales)}${postfix}<b></div>
                                    <div>Max: <b>${formatSalesValue(maxSales)}${postfix}</b></div>
                                </div>
                            `),   
                        };
                    }),
                    {
                        value: totalSales,
                        formattedValue: formatSalesValue(totalSales) + postfix,
                        css: {
                            verticalAlign: 'middle',
                            textAlign: 'center',
                            fontSize: '16px',
                        },
                    },
                ],
            });
        });
        
        module.exports = {head, rows};

  6. Вверху чарта нажмите Выполнить. В области предпросмотра отобразится таблица с данными из подключения со сгруппированными строками по полю PaymentType и сгруппированными колонками по полю OrderYear с графиком по значениям продаж по месяцам:

    image.png

  7. Чтобы сохранить чарт, в правом верхнем углу нажмите Сохранить и введите название чарта.

Предыдущая
Создание таблицы на основе датасета
Следующая
Создание таблицы на основе API Connector