LINUX.ORG.RU

Libre Calc

 ,


0

1

Как в Libre Calc в двух огромных таблицах найти похожие совпадения по дате и сумме, но с небольшой разницей, например, по сумме меньше до 5000, а по дате более максимум на 1-7 дней?


Ответ на: комментарий от crutch_master

сделать выборку, как человек

Допустим у нас есть CREATE TABLE tab1 (id, sum, date); и tab2 такая же. Дата пускай записана в днях от 01.01.1970 (сегодня был бы 18599 день, если считать с единицы).

Я правильно запрос составляю?

WITH t AS (
    SELECT 
		tab1.id AS id1,
		tab2.id AS id2,
		tab1.sum AS sum,
		tab1.date AS date
	FROM 
		tab1, tab2
	WHERE 
		tab1.sum = tab2.sum 
		AND
		tab1.date = tab2.date
) SELECT * FROM 
		t AS A 
	JOIN 
		t AS B 
	ON
		abs(A.sum - B.sum) < 5000 
	AND
		abs(A.date - B.date) <= 7
;
anonymous
()
Ответ на: комментарий от anonymous

более максимум на 1-7 дней

А, нет, походу, я неправильно написал. abs(A.date - B.date) BETWEEN 1 AND 7? Или как? Чёт я не пойму.

anonymous
()

Создай отдельную колонку, или даже две, в обеих таблицах и сформулируй там значения, дающие компьютеру более чёткое определение твоего понятия «похожие совпадения по дате и сумме» - преобразуй твоё понимание во что-то более формальное, которое он мог бы сравнить: значение разницы в принятых тобой единицах, TRUE/FALSE, больше/равно/меньше, -1/0/1, значение±дельта и т.п.

blexey ★★★★★
()
Последнее исправление: blexey (всего исправлений: 1)
Ответ на: комментарий от anonymous

и tab2 такая же.

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

Я правильно запрос составляю?

С твоей структурой:

    SELECT 
		tab1.id AS id1,
		tab2.id AS id2,
		tab1.sum AS sum,
		tab1.date AS date
	FROM 
		tab1, tab2 
--tab1, tab2 даст декартово произведение, т.е. к каждой записи tab1 прицепит все записи из tab2
	WHERE 
--дальше ничего дополнительно ограничивать не надо, просто реализуешь своё условие. Естественно соответствий может быть от нуля до размера tab2 на каждую запись.
     		abs(tab1.sum - tab2.sum) < 5000 
		AND
		abs(tab1.date - tab2.date) <= 7

А, нет, походу, я неправильно написал

Без разницы. Результат abs не будет меньше нуля. Свяжутся все записи у которых разница суммы меньше 5к и разница в дате меньше 8 дней.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Не нужно и нет смысла делать таблицы с одинаковой структурой

Две таблицы — по условию.

дальше ничего дополнительно ограничивать не надо, просто реализуешь своё условие

Ну так условие же не «найти совпадения», а «найти похожие совпадения». Чувствуешь разницу?

А, нет, походу, я неправильно написал

Без разницы. Результат abs не будет меньше нуля. Свяжутся все записи у которых разница суммы меньше 5к и разница в дате меньше 8 дней.

Ты, походу, тоже не понял. 0 — меньше 8, но нам он не подходит. И даже единица не подходит, так как по условию: «более максимум на 1-7 дней» — «более» — видишь? А вот 8 и более, наверное, подойдут. Или нет? Мне пока что-то не очевидно.

anonymous
()
Ответ на: комментарий от anonymous

Ты, походу, тоже не понял.

Вернётся пояснит, что он имел ввиду. Если точное совпадение не нужно достаточно просто добавить and tab1.date <> tab2.date

crutch_master ★★★★★
()
Ответ на: комментарий от GP

Беседа людей, никогда не слышавших про SQLite

Да ты шо? Как так, дожить до 2к20 и ни разу не услышать про sqlite. А нахрен он нужен, кстати?

crutch_master ★★★★★
()
Ответ на: комментарий от crutch_master

Вы будете смеяцо, но на текущем проекте он мне очень сильно пригодился.

У меня еще был Excel и SQL Server.

Угадай для какой цели?

GP
()
Ответ на: комментарий от anonymous

так ведь и ты не знаешь, потому что решения ты не дал, а только сказал бред

Kompilainenn ★★★★★
()
Ответ на: комментарий от crutch_master

Формирование SQL с INSERT ами.

А теперь давайте поговорим о том, почему нельзя использовать стандартный Data Import от SQL Server Management Studio

GP
()
Ответ на: комментарий от anonymous

Libre Calc

Я далека от программирования, поэтому не понимаю о чем идёт речь в комментариях, есть ли в Calc Libre такая хитрая функция, к-я сравнит и подставит в одну строку похожие значения (с небольшой разницей) из разных таблиц? А те которые вообще не совпадают отбросит или пометит, как false или другим способом?

tanyak
() автор топика
Ответ на: комментарий от GP

А теперь давайте поговорим о том, почему нельзя использовать стандартный Data Import от SQL Server Management Studio

Без понятия, ms-подеклу не трогал и, надеюсь, трогать не придётся.

crutch_master ★★★★★
()
Ответ на: Libre Calc от tanyak

есть ли в Calc Libre такая хитрая функция, к-я сравнит и подставит в одну строку похожие значения (с небольшой разницей) из разных таблиц?

В экселе нет таблиц. Там массивы ячеек. Вам кажется, что это таблицы, но на самом деле это не так:)
Сейчас загуглил, как сделать декартово произведение, но там тоже какой-то еритический sql, что листы представляются как источник данных и потом из них как-то там делается выборка. Но если ты и так знаешь sql, то смысл этих манипуляций крайне непонятен.
Короче без основ реляционной алгебры это сложно, позовите программиста.

crutch_master ★★★★★
()
Ответ на: комментарий от crutch_master

Это ты свой писюн трогаешь в туалете.

А c Oracle, SQL Server и PostgreSQL работают.

GP
()
Ответ на: комментарий от crutch_master

Sql

Я так и думала там нужен SQL или Python, что всегда требуется для аналитики данных в больших объемах. Да массивы, я думала массивы и таблицы - это одно и то же)

tanyak
() автор топика
Ответ на: комментарий от anonymous

Сводные таблицы

Спасибо, я просмотрела мельком , там вроде нет того, что надо, но буду более тщательно изучать в любом случае

tanyak
() автор топика

кстати, чего бы не спросить на профильном форуме forumooo.ru ?

Kompilainenn ★★★★★
()
Ответ на: Сводные таблицы от tanyak

А ты же, кстати, можешь открыть или импортировать свои таблицы в Libre Base и там уже мутить любые запросы, навроде тех, что мы выше разбирали. Но там даже должно быть проще и без SQL. Сложность только с разностью дат может быть, но, думаю, это легко организовать в Calc.

anonymous
()

=mround(<дата>, 4) и =mround(<дата> + 3, 4) дадут два числа.
Совпадение любого из этих двух чисел в одной строке с любым из двух в другой даст строки с датами отличающимися не больше чем на 7 дней.

Аналогичным образом округлить суммы два раза на половину желаемого значения.

Из получившихся столбцов наконкатенировать четыре столбца с комбинациями дат и сумм.

Добавить четыре vlookup-а завёрнутых в IF(IFERROR(..., 0), 1, 0).
Суммировать эти столбцы и отфильтровать строки со значением больше 0.

P.S.
Случаи с множественными платежами похожих сумм в требуемом диапазоне дат будут показывать результат предположительно отличающийся от желаемого.
(В одной таблице 15 тыщ в эту среду, а в другой — 10 раз по 12 тыщ с понедельника по пятницу. На обеих сторонах будут найдены «совпадения для всего».)

frob ★★★★★
()
Ответ на: комментарий от frob

наконкатенировать

Вручную что ли конкатенировать собрался? Ты понимаешь, что каждую запись из одной таблицы надо сравнить с каждой записью из другой?

anonymous
()
Ответ на: комментарий от frob

Может я что-то не так понял, но мне было бы крайне интересно увидеть твоё решение для, к примеру, этих двух таблиц:

sum	date		sum	date
10	5		11	6
20	4		22	7
30	3		33	8

Найти: пары строк, отличающиеся по sum не больше, чем на 12 и по date не больше, чем на 3

Результат должен быть таким:

sum	date		sum	date
10	5		11	6
10	5		22	7
20	4		11	6
20	4		22	7

Можешь прямо в .xlsx куда-нибудь выложить, если текстом сложно объяснить будет.

anonymous
()
Ответ на: комментарий от anonymous

vlookup так не работает, эта функция возвращает первое что подошло.

Поэтому, если округлить в две пары значений и слепить, то...

Для левой таблицы найдёт в правой «11 6», «11 6» и «#N/A».
Для правой таблицы в левой — «10 5», «10 5» и «#N/A».
(Оборачиванием в if/iferror и суммированием выделяем строки, для которых нашлось совпадение хотя бы с одним из слепленных значений.)

То есть будет получен ответ на вопрос «для каких транзакций есть что-то похожее в другой таблице». «10 5» похоже на «11 6», а то что оно похоже ещё и на «22 7» останется за кадром.

То, как задача сформулирована ТС-ом, даёт возможность «сделать как попросили», а не так как на самом деле надо =)

frob ★★★★★
()
Ответ на: комментарий от anonymous

Ага, примерно так же, как в линуксе нету папок.

Совсем не также. Принципы работы еклелев вообще мало имеют что общего с принципами реляционной алгебры. В РА нет ни ячеек, ни адресов ячеек, ни ссылок на ячейки, нет фиксированного порядка столбцов/строк и тем более нет таких, как в екселе функций. В свою очередь в екселе нет отношений как в РА, то есть с колонками определённого типа, ключами, записями и всем вытекающим. Можно только пытаться имитировать что-то похожее, но это всё вообще не в какие ворота.

Так что заявлять что «это как разница между папками и каталогами» - это какая-то запредельная грань тупизны.

crutch_master ★★★★★
()
Ответ на: комментарий от frob

Тут нужно декартово, которого в екселе в нормальном виде нет. Это всё - костыли нелепые. Да и сегодня им нужно вот это, а завтра совсем другая выборка и кому-то опять придётся приседать приплясывая ради того, что делается в человеческих условиях с помощью одного запроса. Тут недавно знакомый тоже просил что-то сделать в екселе и очень удивился отказу, потому что думал, что там это так всё легко и просто, а на самом деле - это тупая, неблагодарная и копеечная работа.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от frob

То, как задача сформулирована ТС-ом

Насчёт постановки задачи я с тобой частично соглашусь, но если уж нужно найти совпадения в двух таблицах, то тут точно не подходит vlookup, который «возвращает первое что подошло».

@crutch_master, вон, хоть и дурачок, но даже он это понимает.

anonymous
()
Ответ на: комментарий от crutch_master

А тебя кто заставлял писать «В экселе нет таблиц.»? Писал бы тогда «нет таких таблиц, как в реляционных БД» и тебе бы слова никто не сказал. Опростоволосился и обзывается теперь — типичный регистрант.

anonymous
()
Ответ на: комментарий от anonymous

Ммм... давай подумаем... чтобы это такое могло быть...

Выглядит как сличение выставленных счетов и полученной оплаты.
Разница в суммах — прогулки курсов валют (2..4% в мирное время).
5кр в неделю, если это из-за обменного курса, означает транзакции тыщи в полторы у.е. В задаче сказано, что их МНОГО.

Почему эта лавка древностей информацию о пришедших платежах получает в виде «дата/сумма» без какого-либо указания на источник или назначение платежа?

Если это счета/платежи, то какой смысл искать оплату в дни ПРЕДШЕСТВУЮЩИЕ выставлению счёта?

Если счета отличаются меньше чем вариация курса или «кластеризуются» (тарифные планы, то-сё), то задача становится похожей на предложенную в 00:33:44. А приведённый там же «ответ» не имеет «бухгалтерского» смысла.
(Допустим, на неделе тысяча клиентов заплатила примерно одинаковую сумму (и ещё сто тысяч — всё разное). Сто одна тыща счетов, сто одна тыща платёжек. Для тысячи из каждой группы имеется совпадение «любой с любым», получаем таблицу из миллиона бесполезных строк, в которых потерялись сто тысяч уникальных.)

frob ★★★★★
()
Ответ на: комментарий от frob

Ты верно рассуждаешь. Никакого смысла искать совпадения в предшествующие и последующие дни нет, как и нет смысл искать совпадения по оплате, отличающейся, скажем, на 5к. Только как ты узнаешь, предшествующий это день или нет, если не сверишь две даты? Если даже таблички отсортированы, то пускай лучше компьютер сам ищет совпадения полным перебором, чем я буду всякие алгоритмы бинарного поиска в екселях реализовывать.

получаем таблицу из миллиона бесполезных строк

Думаю, что миллион относительно бесполезных строк всё же лучше одной действительно бесполезной строки.

anonymous
()
Ответ на: комментарий от anonymous

Писал бы тогда «нет таких таблиц, как в реляционных БД»

Если мы говорим о множестве каких-то там типизированных записей и их пересечении, то мы уже по определению говорим о таблицах РБД и ни о чём другом. И юзеры екселя действительно хотят таблицу как в РБД, но у них нет нихрена, их обманули и подсунули неструктурированный массив ячеек без определённого типа, зачем-то назвав это таблицей. Но те ничего не поняли и думают, что это и правда таблица и ей можно пользоваться как таблицей в РБД.

«В экселе нет таблиц.»

Если бы там делали таблицу с датами отпусков или внутренними телефонами, чтобы распечатать приклеить на стенку, то пожалуйста, пусть бы делали себе на здоровье. Но если речь о реляционных данных, то да, в екселе никаких таблиц нет и не было, не натягивайте сову на глобус, пользуйтесь инструментами по назначению.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 5)
Ответ на: комментарий от frob

Зачем?

Затем, что это связь одного ко многим, следовательно, множества придётся перемножать. Или что надо делать? Просирать часть получившейся выборки? По какому критерию?

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 2)
Ответ на: комментарий от crutch_master

как в РБД

Ну так а в РБД зато нет таких таблиц, как в екселе. Более того, если следовать твоей логике, в РБД вообще нет таблиц, там есть только структуры, их взаимоотношения и данные, когерентные структурам или множества. Реляционная алгебра не таблицами же оперирует. А то, что ты видишь какую-то там табличку, когда делаешь SELECT, так это имеет только весьма посредственное отношение к РБД: другой увидит множество точек на плоскости, список товаров в интернет-магазине или вовсе какое-то одно значение, которое уж точно будет неуместно завёрстывать в таблицу.

anonymous
()
Ответ на: комментарий от anonymous

Ну так а в РБД зато нет таких таблиц, как в екселе.

А тред у нас как раз таки про таблицы, как в РБД, а не про срань с ячейками, ссылками и формулами, которая для описываемых задач не подходит полностью.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Короче без основ реляционной алгебры это сложно, позовите программиста.

такого же тупоголового выебщ%ка как ты?

anonymous
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.