Как сделать такую выборку в oracle sql?

Question

[Кирилл Журавлев]

Господа. Есть вот такая база.



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

Пока что я сделал самое простое, это выборка по клиентам у которых потребление меньше среднего по всем.

select n_client from computation where (n_info_cold + n_info_hot) < (select avg(n_info_cold + n_info_hot) from computation) потребление воды меньше среднего

Пытаюсь достать выборку по первой букве фамилии:

select c_last_name from client where lower(c_last_name) in ('а%', 'б%', 'в%', 'г%', 'д%', 'е%', 'ё%', 'ж%', 'з%', 'и%', 'к%', 'л%', 'м%', 'н%', 'о%', 'п%', 'р%', 'с%', 'т%', 'у%', 'ф%', 'х%', 'ц%', 'ч%', 'ш%', 'щ%', 'э%', 'ю%', 'я%')

но на выходе "no data found".

Конечно так работает:

select c_last_name from client where lower(c_last_name) like 'а%'

но это для одной буквы.

Привожу описание семантики полей:

Таблица CLIENT – данные об абонентах.
•	N_CLIENT – лицевой счет
•	C_FIRST_NAME – имя
•	C_SECOND_NAME – отчество
•	C_LAST_NAME – фамилия
•	N_SEX – идентификатор пола
•	N_CITY – идентификатор города или поселка
•	N_STREET – идентификатор улицы
•	C_HOUSE – номер дома и номер корпуса
•	 N_FLAT – номер квартиры
•	N_PHONE – номер телефона

Таблица SEX – справочник пола
•	N_SEX – идентификатор пола
•	C_SEX – название пола

Таблица STREET – справочник улиц
•	N_STREET – идентификатор улицы
•	C_STREET – название улицы

Таблица CITY – справочник городов и поселков
•	N_CITY – идентификатор города или поселка
•	C_CITY – название города или поселка

Таблица PAYMENT – оплата абонента
•	N_CLIENT – лицевой счет абонента
•	D_PAY – дата оплаты
•	N_SUM – сумма оплаты
•	N_SIGN – признак(1 – оплата учтена при начислении, 0 – оплата не учтена)

Таблица COMPUTATION – начисления абонентов
•	N_COMPUTATION – идентификатор начисления
•	N_CLIENT – лицевой счет абонента
•	D_COMPUTATION – дата начисления
•	N_INFO_HOT – текущие показания счетчика горячей воды
•	N_INFO_COLD – текущие показания счетчика холодной воды
•	N_DIFFERENCE_HOT – разница текущих и предыдущих показаний счетчика горячей воды
•	N_DIFFERENCE_COLD – разница текущих и предыдущих показаний счетчика холодной воды
•	N_TARIFF – идентификатор тарифов на горячую и холодную воду
•	N_SUM_HOT – начисление за горячую воду за текущий период
•	N_SUM_HOT – начисление за холодную воду за текущий период
•	N_DEBT – долг на начало периода
•	N_PAY – оплата за предыдущий период
•	N_TOTAL – итоговая сумма на конец месяца

Таблица TARIFF – тарифы на горячую и холодную воду
•	N_TARIFF – идентификатор тарифов по холодной и горячей воде
•	D_TARIFF – дата смены тарифов на новые
•	N_TARIFF_HOT – тариф на горячую воду (в руб.)
•	N_TARIFF_COLD – тариф на холодную воду (в руб.)

Примечание: первая буква в названии поля обозначает тип данных этого поля
N – NUMBER, D – DATE, C – CHAR (VARCHAR2)

Comments

[Кирилл Журавлев]

Если вдруг кому пригодится. Вот рабочее решение.

select distinct
   a.first_letter_fam,
   a.avg_plateg_by_letter 
from
   (
      select
         substr(n.c_last_name, 1, 1) as first_letter_fam,
         avg(p.n_sum) over (partition by substr(n.c_last_name, 1, 1)) as avg_plateg_by_letter,
         (
            n_info_cold + n_info_hot
         )
         as potr_vody,
         avg(n_info_cold + n_info_hot) over () as avg_potr_vody_all 
      from
         computation c,
         client n,
         payment p 
      where
         c.n_client = n.n_client
      and
         p.n_client = n.n_client   
)
   a 
where
   a.potr_vody < a.avg_potr_vody_all

order by  a.first_letter_fam

Answer 1

[Denis Holub]

Смотрите аналитические функции.
Посмотрите мой запрос и поэкспериментируйте у себя на базе.

Поле месячного платежа я не понял какое, поэтому сами подставите где написано.

select a.first_letter_fam, a.avg_plateg_by_letter
from (
select substr(n.c_last_name,1,1) as first_letter_fam, 
avg(подставить_сюда_полe_месячного платежа) 
    over (partition by substr(n.c_last_name,1,1)) as avg_plateg_by_letter,
(n_info_cold + n_info_hot) as potr_vody,
avg(n_info_cold + n_info_hot) over () as avg_potr_vody_all
from computation as c, client as n
where c.n_client = n.n_client) as a
where a.potr_vody < a.avg_potr_vody_all

Comments

[Кирилл Журавлев]

select a.first_letter_fam, a.avg_plateg_by_letter
from (select substr(n.c_last_name,1,1) as first_letter_fam, avg(p.n_sum) over (partition by substr(n.c_last_name,1,1)) as avg_plateg_by_letter, (n_info_cold + n_info_hot) as potr_vody, avg(n_info_cold + n_info_hot) over () as avg_potr_vody_all
from computation as c, client as n, payment as p
where c.n_client = n.n_client) as a
where a.potr_vody < a.avg_potr_vody_all

Получилось вот так. внутренний подзапрос выходит с ошибкой ORA-00933: SQL command not properly ended

Весь запрос полностью с ошибкой ORA-00907: missing right parenthesis

[Denis Holub]

Кирилл Журавлев, это конечно маловероятная (но скобак я нигде не нахожу недостающих) причина, но попробуйте поубирать везде as У меня иногда на работе оракл на это ругается.

[Denis Holub]

Конкретно напротив названия таблиц AS удалите. У полей должен пройти.