Объединить 2 таблицы, join tables, FULL OUTER JOIN

Обсуждение программ nnCron и nnCron LITE

Объединить 2 таблицы, join tables, FULL OUTER JOIN

Postby spronkin » Thu, 17 Nov 2016, 02:39

Добрый день! Есть 2 таблицы, разделитель TAB.
Code: Select all
c:\nncron>cat A.txt
Lubos    John    Linda    Rares    Rick
         A       B        C        E
4        1       2        3

Code: Select all
c:\nncron>cat B.txt
Anna    Linda    Rares    John     Max
        F        G        E        I
4       2        3        1       

Таблицы не отсортированы. Нужно объединить их так, чтобы получить результирующую таблицу:
Code: Select all
c:\nncron>cat C.txt
Lubos    John    Linda    Rares    Rick    Anna    Max
         A       B        C        E               
4        1       2        3                       
         E       F        G                        I
         1       2        3                4       

Насколько я понимаю, эта операция в терминологии баз данных называется FULL OUTER JOIN (полным объединением). Вот хотелось бы узнать, как его эффективно реализовать в nncron. Пока получилось стандартными утилитами unixutils - gawk sed, sort, join.У последный я нашел 2 существенных недостатка. Во первых, сравниваемые столбцы таблиц должны быть отсортированы по возрастанию плюс выполняется объединение строк, а не полей. То есть, для того, чтобы воспользоваться данной утилитой, эти таблицы нужно транспонировать, добавить недостающие TAB справа и отсортировать по сравниваемому полю.
В винде я столкнулся еще с одной промежуточной проблемой - невозможность использования TAB в качестве выходного разделителя (ключ \t), но это так, мелочи, опустим это.
Кроме того, если в исходной таблице идут 2 символа разделителя подряд, например 2 TAB, то в объединенной таблице будет один TAB. Опция -e, замещающая входные пустые ячейки, работает некорректно, заменяются не все пустые ячейки. Поэтому пустые ячейки исходной таблицы приходится заменять с помощью sed.
Поскольку исходные таблицы были отсортированы, чтобы привести объединенную таблицу в начальный, не сортированный вид, строки транспонированных таблиц нужно было еще и пронумеровать до сортировки, чтобы потом их восстановить...
Может кому пригодится:
Code: Select all
#( Join2Tables
SingleInstance
NoActive
Action:
    \ в комментариях приведены состояния стека таблиц A размером 3x5 и B размером 4xY
    \ sed ver. 4.0.7, sed_ ver. 4.2.1
    SWHide
    QUERY: "Обнулить общую таблицу?"
    IF S" B.txt" FDELETE THEN
    START-APPW: "paste_.exe | sed -e "${/^$/d};s/[^\t]//g" | sed -e "${/^$/d}" | sed_ -z "$s/\n$//" | gawk "{print length ($0)}" | sort -nr | sed -e "$=;1!d" | sed_ -z "s/\r/\x0D/" > tab_row_counts.txt"
    READ-BY-LINE: tab_row_counts.txt
        FOUND-LINE S>NUM 1+
    ;READ-BY-LINE \ 4 5
    \ 2 5
    SWAP DUP 2 = IF ELSE SWAP THEN 1- 2DUP 2DUP DROP \ 5 3 5 3 5
    \ 5 3 5 прибавляем недостающие табы справа, транспонируем и нумеруем общую таблицу
    START-APPW: "paste_ | sed -e "${/^$/d}" | gawk -F"\t" -v OFS="\t" "NF=%0 esPICK%" | gawk -F\x09 "{OFS = FS}{ for (i=1; i<=NF; i++) print i,NR,$i}" | sort -nk1,1 -k2,2 | gawk -F\x09 " NR>1 && $2==1 { print \"\" }; { printf \"%PERCENT%s\x09\",$3 }; END { print \"\" }" | cut -f 1-%1 esPICK% | cat -n - B.txt | sed -e "s/\t$/\t987879798897/;s/\t\t/\t987879798897\t/g;s/\t\t/\t987879798897\t/g;s/\(^ *\)\|\x0D//;s/ /\x1B/g" > out.txt"
    \ 3 5 Фильтруем строки с номерами и сортируем их, создаем таблицу A для объединения
    START-APPW: "sed -e "%0 esPICK%q" out.txt | sort -k2 > A.txt"
    \ создаем значение ключа -o для join
    S" 1.1,2.1,0" \ c a 3 5
    \ Кладем на стек число столбцов таблицы B
    START-APPW: "sed -e "${/^$/d};s/[^\t]//g" B.txt | sed_ -z "$s/\n$//" | gawk "{print length ($0)}" | sort -nr | sed -e "1q" > tab_row_counts.txt"
    S" tab_row_counts.txt" FILE S>NUM 1+ \ 4 c a 3 5 | 1 c a 3 5
    \ Если файл пустой, на стеке 1 - выбрасываем, нет - дополняем значение ключа
    DUP 1 > IF \ 6 c a 3 5
        2+ 3 DO S" ,1." S+ I N>S S+ LOOP \ c a 3 5
    ELSE DROP THEN ROT \ 1 c a 5
    \ дополняем значение ключа
    DUP 1 > IF
        2+ 3 DO S" ,2." S+ I N>S S+ LOOP \ c a 5
    ELSE DROP THEN ROT \ 5 c a
    \ Выделяем строки таблицы B, сортируем их и делаем FULL OUTER JOIN с таблицей A, возвращаем пробелы, удаляем номера, результат помещаем в таблицу B:
    START-APPW: "sed -e "1,%0 esPICK%d" out.txt | sort -k2 | join -j 2 -a1 -a2 -o %2 esPICKS% -e 987879798897 - A.txt | sort -nk1,1 -k2,2 | sed -e "s/ /\t/g;s/987879798897//g;s/\x1B/ /g" | cut -f 3- > B.txt"
    \ транспонируем, удаляем TAB в конце строки, результат помещаем в join.txt и буфер
    START-APPW: "gawk -F\x09 "{OFS = FS}{ for (i=1; i<=NF; i++) print i,NR,$i}" B.txt | sort -nk1,1 -k2,2 | gawk -F\x09 " NR>1 && $2==1 { print \"\" }; { printf \"%PERCENT%s\x09\",$3 }; END { print \"\" }" | sed "s/\t$//" > join.txt && clip < join.txt"
    BALLOON: "Join2Tables" "Готово!"
)#

Исходный порядок строк навел с помощью нумерации обеих таблиц и численной сортировки по двум столбцам. Работает и с однострочными таблицами.
User avatar
spronkin
 
Posts: 86
Joined: Sun, 15 Jan 2012, 13:56

Re: Объединить 2 таблицы, join tables, FULL OUTER JOIN

Postby spronkin » Thu, 24 Nov 2016, 07:43

Работает, но плохо. Какая же гадость этот параметр -o... Генерация -o ни к черту не годится. Зачем его только придумал автор? Можно было бы еще немного потрудиться и дать возможность пользователям указывать диапазон полей для вывода через дефис (тире). При работе с большими таблицами длина этого ключа легко может превысить максимально допустимый размер команды (8192 байт). Да и очень коряво выглядит конструкция -o 1.1,2.1,0,1.3,1.4,...,1.XXXX,2.3,2.4,...,2.XX. Пришлось его выкинуть.
Так я думаю получше будет:
Code: Select all
#( Join2Tables
SingleInstance
NoActive
Action:
    SWHide
    QUERY: "Стереть общую таблицу?"
    IF S" B.txt" FCREATE THEN
    START-APPW: "paste_.exe | sed_ -e "${/^$/d};s/[^\t]//g" | gawk -F\t "END { print NR\"\n\"NF }" | sed_ -e "$s/^0/1/" > tab_row_counts.txt"
    \ 1st line: Columns A (CA), 2nd line: Rows A (RA). CA-1 CA RA:
    READ-BY-LINE: "tab_row_counts.txt" FOUND-LINE S>NUM ;READ-BY-LINE SWAP DUP 1-
    \ Число столбцов и строк таблицы B на стек: RA CA RB CB CB CA-1:
    1 READ-BY-LINE: "B.txt" FOUND-LINE S" /\t/" RE-ALL 1+ ;RE-ALL RBL-EXIT ;READ-BY-LINE DUP 0 FOR-FILE-LINES: "B.txt" 1+ ;FOR-FILE-LINES 1- 2ROT SWAP
    \ прибавляем недостающие табы справа, транспонируем, нумеруем общую таблицу, добавляем метки пробелов, номеров строк, пустых полей:
    START-APPW: "paste_ | gawk -F"\t" -v OFS="\t" "NF=%0 esPICK%" | gawk -F\x09 "{OFS = FS}{ for (i=1; i<=NF; i++) print i,NR,$i}" | sort -nk1,1 -k2,2 | gawk -F\x09 " NR>1 && $2==1 { print \"\" }; { printf \"%PERCENT%s\x09\",$3 }; END { print \"\" }" | cut -f 1-%1 esPICK% | sed -e "s/$/\t/;s/\t/&NULL4/" | cat -n B.txt - | sed -e "s/^ *//;s/ /NULL1/g;s/\t$/&NULL2/;:a;s/\t\t/\tNULL2\t/g;ta;s/^\([^\t]*\)\t\([^\t]*\t\)/\2\1NULL3/" > out.txt"
    \ Сортируем строки с номерами, создаем таблицу A для объединения: RB RB CB CB CA-1
    DUP FILE-EMPTY: "B.txt"
        IF
            DROP 2DROP + 1+ \ Если общая таблица была стерта, копируем в нее таблицу А, добавляем недостающие TAB: CB+CA
            START-APPW: "sed -e "s/[0-9]\+NULL3\|NULL2//g;s/NULL1/ /g;s/NULL4//" out.txt | gawk -F"\t" -v OFS="\t" "NF=%0 esPICK%" > B.txt"
        ELSE
            START-APPW: "sed -e "1,%0 esPICK%d" out.txt | sort -k1 > A.txt"
           \ Выделяем строки таблицы B, сортируем их и делаем FULL OUTER JOIN с таблицей A, возвращаем пробелы, удаляем номера, результат помещаем в таблицу B: RB CB CB+CA-1
            2SWAP + ROT ROT START-APPW: "sed -e "%0 esPICK%q" out.txt | sort -k1 | join -j 1 -a1 -a2 -e NULL2 - A.txt | sed -e "s/ /\t/g" | sort -nk2 | sed -e "/NULL4/!s/$/\tNULL4/;s/NULL1/ /g;s/[0-9]\+NULL3//g;:a;/^\(.\+\t\)\{%1 esPICK%\}NULL4/!s/NULL4/NULL2\t&/;ta;s/NULL2\|\tNULL4//g" | gawk -F"\t" -v OFS="\t" "NF=%2 esPICK%" > B.txt"
        THEN
    \ транспонируем, удаляем последнюю пустую строку с TAB, результат помещаем в join.txt и буфер:
    START-APPW: "gawk -F\x09 "{OFS = FS}{ for (i=1; i<=NF; i++) print i,NR,$i}" B.txt | sort -nk1,1 -k2,2 | gawk -F\x09 " NR>1 && $2==1 { print \"\" }; { printf \"%PERCENT%s\x09\",$3 }; END { print \"\" }" | sed "s/\t$//;$d" > join.txt && clip < join.txt"
    BALLOON: "Join2Tables" "Готово!"
)#

Пользоваться так. Скопировали одну таблицу, выполнили задачу, скопировали следующую, опять выполнили задачу и так далее. На выходе должна получиться объединенная таблица. Заголовки объединяемых таблиц должны быть в верхней строке.
UPD: поправил скрипт, должно работать с таблицами любых размеров.
Last edited by spronkin on Wed, 21 Dec 2016, 05:50, edited 4 times in total.
User avatar
spronkin
 
Posts: 86
Joined: Sun, 15 Jan 2012, 13:56

Re: Объединить 2 таблицы, join tables, FULL OUTER JOIN

Postby elos » Tue, 29 Nov 2016, 14:18

Ниже лично моё мнение...
В своё время использовал GAWK из-за более развитых средств работы со строками(чтобы не мучиться в кроне на тот период) по регэкспам. Но не более! Там же, вроде бы, и построчное чтение файла есть. Он же и предназначен для построчного разбора и обработки входного потока (например, текстового файла) по заданным шаблонам.

Но! Почему бы для работы с таблицами не использовать что-то консольное из той же сферы? В частности - SQLite. Тем более, что он поддерживает импорт и текстовые sql-файлы команд.

Лично я стараюсь для каждой задачи найти подходящий для неё инструмент. (Понятно, что нормально заточенным ножом можно бриться, но лучше делать это бритвой, и желательно безопасной...)
А крон использовать лишь для запуска, чем он практически у тебя и занимается по большей части.

Описание объединения таблиц можно найти что на русских уроках, что на английском. Как будет выглядеть вывод - не знаю, но из поверхностного чтения ясно, что он настраиваемый.
elos
 
Posts: 665
Joined: Tue, 25 Apr 2006, 11:15

Re: Объединить 2 таблицы, join tables, FULL OUTER JOIN

Postby spronkin » Sun, 04 Dec 2016, 01:06

elos wrote:Почему бы для работы с таблицами не использовать что-то консольное из той же сферы? В частности - SQLite.

Elos, спасибо за ценный совет! Я думаю, это как раз то, что нужно. Это что же получается - можно работать с базами данных напрямую из консольного менеджера? Круто! И наверное можно использовать SQL команды и писать скрипты на SQL для обработки таблиц в БД? Находка!
Правда придется проштудировать немало статей, но ссылки вы уже указали, осталось только заняться обучением). :prayer:
PS: Join там точно есть, думаю и FULL OUTER тоже.
User avatar
spronkin
 
Posts: 86
Joined: Sun, 15 Jan 2012, 13:56


Return to nnCron forum (Russian)

Who is online

Users browsing this forum: No registered users and 8 guests

cron