Помогите с задачкой ;)
6262
26
AKIRO2
guru
ЗАДАЧА 1: Имеется компания которая продает свой сервис. Сервис действует втечение 30 дней с момента оплаты, и отключается автоматически по истечению 30 дней. Если у Клиента работает сервис, Клиент считается действующим, если сервис не работает, Клиент считается не действующим. При каждой оплате, автоматически, в базу данных заносится НОВАЯ строчка, которая включает в себя, ФИО Клиента и дату. Предложите алгоритм (последовательность действий) как используя базу данных найти всех не действующих Клиентов.
Какая БД? ну я о синтаксисе, возможны ли подзапросы?
Через подзапрос соображу, а во тчерез join чего-то не могу сообразить...
SELECT DISTINCT fio
FROM table
WHERE fio NOT IN (SELECT fio FROM table WHERE (DATE() - table.date) <= 30)
Через подзапрос соображу, а во тчерез join чего-то не могу сообразить...
SELECT DISTINCT fio
FROM table
WHERE fio NOT IN (SELECT fio FROM table WHERE (DATE() - table.date) <= 30)
связать все не действующие сервисы с клиентами, получившийся список клиентов будет списком не действующих клиентов.
Если информация о сервисах не храниться в базе, её нужно будет получить.
Таблица куда заноситься информация о клиентах получается бесполезной, так как в ней мешанина из действующих и не действующих клиентов.
Если информация о сервисах не храниться в базе, её нужно будет получить.
Таблица куда заноситься информация о клиентах получается бесполезной, так как в ней мешанина из действующих и не действующих клиентов.
Таблица куда заноситься информация о клиентах получается бесполезной, так как в ней мешанина из действующих и не действующих клиентов.
- вы предлагаете на две таблицы клиентов разделить: "Живые" и "Мертвые"?
клиент сегодня действующий, завтра нет, через месяц снова действующий...
а может атрибут прицеплять к ФИО? если дата.оплата <=30, то - "живой" клиент, нет - "умер".
Задача: максимально быструю выборку сделать.
- вы предлагаете на две таблицы клиентов разделить: "Живые" и "Мертвые"?
клиент сегодня действующий, завтра нет, через месяц снова действующий...
а может атрибут прицеплять к ФИО? если дата.оплата <=30, то - "живой" клиент, нет - "умер".
Задача: максимально быструю выборку сделать.
В 1С это называется срез последних/первых.
Делаться запрос где таблица соединяться сама с собой (внутреннее соединение)/ или подзапрос. Пример на подзапросе: Выбрать ФИО из Таблица где ФИО не в (Выбрать ФИО из Таблица где дата>= (текДата-30 дней))
Делаться запрос где таблица соединяться сама с собой (внутреннее соединение)/ или подзапрос. Пример на подзапросе: Выбрать ФИО из Таблица где ФИО не в (Выбрать ФИО из Таблица где дата>= (текДата-30 дней))
Для ускорения можно сделать вторую таблицу (Статус клиента). Повешать тригер на запись в таблицу. ежедневно запускать пересчет состояние клиентов. Будет быстрей наверно.
- вы предлагаете на две таблицы клиентов разделить: "Живые" и "Мертвые"?Сделайте нормализацию БД, т.е. реквезиты клиента в одной таблице, дата по оплате, в другой, связь по коду.
клиент сегодня действующий, завтра нет, через месяц снова действующий...
а может атрибут прицеплять к ФИО? если дата.оплата <=30, то - "живой" клиент, нет - "умер".А может завести еще одну таблицу, куда складывать записи дат с кодом у которых 30 дней истекло и раз в сутки скриптом туда загонять данные из таблицы живых ? Тогда выборка будет максимально быстрой.
Сделайте нормализацию БД, т.е. реквезиты клиента в одной таблице, дата по оплате, в другой, связь по коду.Интересно, как это поможет в данной задаче? да и что тут ненормализировано на ваш взгляд? (ну вот буквально в указанной постановке)?
А может завести еще одну таблицу, куда складывать записи дат с кодом у которых 30 дней истекло и раз в сутки скриптом туда загонять данные из таблицы живых ? Тогда выборка будет максимально быстрой.И быть завязанным на стабильность работы скрипта, т.е. некой обертки.
Не, если такие запросы хочется делать часто (что сомнительно) - тогда смысл, конечно, есть.
А если раз в месяц/год, то особого смысла городить городульку не вижу. Задача вполне решается одним запросом. Тем более, что клиент вполне может и "ожить", значит надо из списка "мертвых" его удалять не забыть.
а может атрибут прицеплять к ФИО? если дата.оплата <=30, то - "живой" клиент, нет - "умер".Оплат-то несколько, надо выбирать последнюю по времени.
Задача: максимально быструю выборку сделать.Вы что же, каждые 5 минут будете такие запросы выполнять??
Интересно, как это поможет в данной задаче? да и что тут ненормализировано на ваш взгляд?Почитайте для начала про нормализацию реляционных БД. Тогда, возможно, вопросы отпадут сами собой.
И быть завязанным на стабильность работы скрипта, т.е. некой обертки.Точно также, как от ввода данных об оплате, блокирующие работу клиента при неоплате etc...
Задача вполне решается одним запросом.А если строк не десять тыщь, а десять миллионов, да причем в ненормализованной базе, время выборки будет караул просто.
Тем более, что клиент вполне может и "ожить", значит надо из списка "мертвых" его удалять не забыть.В нормализованной базе, какраз, ничего удалять ненужно, будем иметь полную историю, и актуальность текущих клиентов.
Почитайте для начала про нормализацию реляционных БД. Тогда, возможно, вопросы отпадут сами собой.Еще раз: напишите где в данной постановке отсутствие нормализации.
Посылать не надо. Нечего ответить - так и скажите.
В нормализованной базе, какраз, ничего удалять ненужно, будем иметь полную историю, и актуальность текущих клиентов.Отсюда поподробнее, пожалуйста. Что за такая структура должна для этого быть? я что-то пропустил, вероятно, в ваших ответах.
Еще раз: напишите где в данной постановке отсутствие нормализации.Автор в первом посте писал:
При каждой оплате, автоматически, в базу данных заносится НОВАЯ строчка, которая включает в себя, ФИО Клиента и дату.Данная БД есть ненормализованная, т.к. ФИО клиента будет с некоей переодичностью повотрятся, для номализации, как я уже предлагал выше, нужно в БД создать 2 таблицы, 1-я - реквезиты клиента, 2-я - дата оплаты.
Посылать не надо. Нечего ответить - так и скажите.Какраз таки надо, многих вопросов у вас невозниклобы, знай вы хотябы азы теории. Из ваших постов я вижу, что теорию вы незнаете вообще, и через это вести конструктивный диалог, крайне затруднительно.
Отсюда поподробнее, пожалуйста.Да кудауж подробней. Имеем 3 таблицы. 1-я ФИО, 2-я у кого срок после оплаты непревышет 30 дней, 3-я у кого срок оплаты превысел 30 дней.
Для выборки всех клиентов с отключенной услугой, достаточно выбрать всех из первой таблицы, невошедших во вторую.
Для истории платежей, ели требуется, работает с первой и третьей таблицами.
Данная БД есть ненормализованная, т.к. ФИО клиента будет с некоей переодичностью повотрятся, для номализации, как я уже предлагал выше, нужно в БД создать 2 таблицы, 1-я - реквезиты клиента, 2-я - дата оплаты.Т.е. повторяющийся ID вы не считаете деноормальзацией, а ФИО (нигде не написано, что это несколько полей) - считаете? удивительно.
Да кудауж подробней. Имеем 3 таблицы. 1-я ФИО, 2-я у кого срок после оплаты непревышет 30 дней, 3-я у кого срок оплаты превысел 30 дней.У вас удивительное представление о нормализации данных и структуре таблиц. Мне действительно до вашей теоретической подкованности как до луны.
Для истории платежей, ели требуется, работает с первой и третьей таблицами.
Я уж молчу про то, как же мы все эти таблицы вести-то будем. Вы как-то об этом скромно умалчиваете.
Для выборки всех клиентов с отключенной услугой, достаточно выбрать всех из первой таблицы, невошедших во вторую.Только честно: вы про JOIN таблицы с самой собой в курсе?
Т.е. повторяющийся ID вы не считаете деноормальзациейПосле этой фразы, продолжать с вами дискуссию, считаю, бессмысленно.
Вот и славно. Я рад, что вы поняли некорректность ваших суждений.
И очень надеюсь, что мы с вами под ID понимаем ID клиента.
И очень надеюсь, что мы с вами под ID понимаем ID клиента.
Я рад, что вы поняли некорректность ваших суждений.Я понял, что вы так и неудосужились ознакомится с теорией, раз так, то и продолжать бессмысленно.
Надеюсь, вы таки ответ автору на поставленную задачку дадите? или вы специалист только по полному переустройству?
Автору я дал совет, использовать его или нет, он решит сам.
а если клиент предоплату внес за 3 месяца?
таблица ФИО привязывается с таблицей оплаты - тогда нет повторов ФИО клиентов
Текущ.Дата - ДатаОплаты <=30 дням
тогда нужно наверно ввести не 30 дней, а "срок жизни" клиента, например: 90 дней
таблица ФИО привязывается с таблицей оплаты - тогда нет повторов ФИО клиентов
Текущ.Дата - ДатаОплаты <=30 дням
тогда нужно наверно ввести не 30 дней, а "срок жизни" клиента, например: 90 дней
а если клиент предоплату внес за 3 месяца?А если за 5? а за 12?
таблица ФИО привязывается с таблицей оплаты - тогда нет повторов ФИО клиентовИ что? есть повторы ID клиентов.
тогда нужно наверно ввести не 30 дней, а "срок жизни" клиента, например: 90 днейЯ бы назвал это "количество оплаченных дней". Ибо клиент как таковой по истечении их не умирает, хоть сервис ему и перестают предлставлять.
Правда, тут надо уточнить: клиенты у вас таки дни оплачивают или календарные месяцы?
Впрочем, это все уже детали. Идею решения вам здесь уже дали, дальше частности, которые вы легко доделаете сами.
таблица ФИО привязывается с таблицей оплаты - тогда нет повторов ФИО клиентовID могут (и должны) повторяться.
И что? есть повторы ID клиентов.
Уникальное ФИО на первый взгляд сойдет за ID. А на второй - я бы дал по рукам, кто так делает. Разведутся разные "Сергей Иванов", "Иванов Сергей" и "Иванов Сергей Иванович".
Уникальное ФИО на первый взгляд сойдет за ID. А на второй - я бы дал по рукам, кто так делает. Разведутся разные "Сергей Иванов", "Иванов Сергей" и "Иванов Сергей Иванович".Вы первый топик перечитайте.
В условиях задачи было "поле ФИО", а вопрос сформулирован "как сделать выборку", а не как реорганизовать структуру БД. Я бы в случае чего вероятно тоже так сформулировал вопрос, дабы сократить условие, не потеряв суть задачи.
Что характерно, на исходный вопрос, как обычно, ответили пара человек, остальные сугубо советами засыпали, как на красной площади.
Вы первый топик перечитайте.Если вопрос сугубо академический, то вы правы, уважаемый коллега.
В условиях задачи было "поле ФИО", а вопрос сформулирован "как сделать выборку", а не как реорганизовать структуру БД.
Но мы кажется выяснили, что тут есть практическая подоплека, и значит советы даем практические.
Триннадцатый
member
Задача проста как мир.
Условно есть два варианта событий - клиент оплачивает ежемесячно и клиент оплачивает раз в не известно какой период, но сразу за весь период.
Второй вариант имеет два под варианта, когда в один период (месяц) вносится оплата за несколько месяцев перед разными записями, либо одной.
Соответственно, чтобы выбрать всех не действующих клиентов нужно:
выбрать все даты, из этих дат достать месяц и год, сформировать периоды из этих дат (строка месяц-год), а затем сформировать таблицу с двумя колонками - одна начало периода, вторая его завершение. Отсортировать по убыванию и выбрать топ 2. Затем выбрать всех клиентов, у которых в эти топ 2 записи нет ни одной оплаты. Это условно гарантированные неактивные клиенты. Условно - из-за второго варианта.
По этим клиентам необходимо посчитать количество платежей и убрать из выборки тех, у кого количество платежей больше количество периодов - 2.
Остался вариант 2.2 - для этого надо знать стоимость сервиса и из выборки, полученной выше, убрать те записи, у которых суммарная оплата превышает расчетную по всем периодам минус 2 периода.
В результате получим гарантированно неактивных клиентов.
Если действовать точно по условию задачи, то варианта 2.2. не предполагается, так как не указан факт известности стоимости сервиса.
Условно есть два варианта событий - клиент оплачивает ежемесячно и клиент оплачивает раз в не известно какой период, но сразу за весь период.
Второй вариант имеет два под варианта, когда в один период (месяц) вносится оплата за несколько месяцев перед разными записями, либо одной.
Соответственно, чтобы выбрать всех не действующих клиентов нужно:
выбрать все даты, из этих дат достать месяц и год, сформировать периоды из этих дат (строка месяц-год), а затем сформировать таблицу с двумя колонками - одна начало периода, вторая его завершение. Отсортировать по убыванию и выбрать топ 2. Затем выбрать всех клиентов, у которых в эти топ 2 записи нет ни одной оплаты. Это условно гарантированные неактивные клиенты. Условно - из-за второго варианта.
По этим клиентам необходимо посчитать количество платежей и убрать из выборки тех, у кого количество платежей больше количество периодов - 2.
Остался вариант 2.2 - для этого надо знать стоимость сервиса и из выборки, полученной выше, убрать те записи, у которых суммарная оплата превышает расчетную по всем периодам минус 2 периода.
В результате получим гарантированно неактивных клиентов.
Если действовать точно по условию задачи, то варианта 2.2. не предполагается, так как не указан факт известности стоимости сервиса.