Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Запрос для выборки.

Posted Ноябрь 26th, 2015 by KsenZ
in

Добрый день.
Имеется самописная биллинг система, которая досталась мне по наследству. И сейчас требуется сделать выборку для 1С, из этого биллинга.

Начну с данных которые имеются.

Используйте данную ссылку, что бы развернуть или свернуть блок текста
TABLE clife - основная таблица описывающая услуги предоставляемые абоненту, варианты оплаты итд.
cid          int4    --  Лицевой счет
dton         date    -- это первый день, когда запись вступила в силу (информация содержащаяся в этой записи стала актуальной)
dtoff        date    -- последний день "жизни" записи
tid          int4    -- ИД тэга из таблицы tags: идентифицирует что именно описано в поле value данной записи
value        text    -- значение тэга, конкретная информация на тему tid
 
TABLE dylog - суммарная информация из таблицы log за каждый день по каждой услуге для каждого абонента.
dt           date    -- дата предоставления услуги
sid          int4    -- идентификатор услуги: её ИД, либо nick-name
cid          int4    -- ЛС абонента
cnt          int4    -- количество записей для данных dt, sid, cid
qnt          bnum    -- объём предоставленной услуги в тарификационных единицах
cost         bnum    -- стоимость в "попугаях"

Небольшой набор данных, для примера, которые имеются в этих таблицах.

clife:

Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT * FROM clife WHERE cid='1425' AND now() BETWEEN dton AND dtoff AND value<>'0';
 cid  |    dton    |   dtoff    | tid |       value
------+------------+------------+-----+-------------------
 1425 | 2014-11-26 | 9999-12-31 |   2 | 4
 1425 | 2014-11-26 | 9999-12-31 |   0 | 116
 1425 | 2015-11-17 | 9999-12-31 |  26 | 10000
 1425 | 2014-11-26 | 9999-12-31 |  11 | rtk
 1425 | 2014-11-26 | 9999-12-31 |  23 | 123
 1425 | 2014-11-26 | 9999-12-31 |  15 | 83.234.161.176/32
 1425 | 2014-12-15 | 9999-12-31 |  27 | gw::em4
 1425 | 2014-12-15 | 9999-12-31 |  28 | 261
 1425 | 2014-11-26 | 9999-12-31 |  29 | 1
 1425 | 2014-11-26 | 9999-12-31 |  40 | 1
(10 rows)
 
db=# SELECT * FROM clife WHERE cid='1564' AND now() BETWEEN dton AND dtoff AND value<>'0';
 cid  |    dton    |   dtoff    | tid |      value
------+------------+------------+-----+------------------
 1564 | 2015-09-15 | 9999-12-31 |  26 | 5000
 1564 | 2015-08-24 | 9999-12-31 |  15 | 83.234.161.39/32
 1564 | 2015-08-24 | 9999-12-31 |  27 | gw::em4
 1564 | 2015-08-27 | 9999-12-31 |  28 | 1365
 1564 | 2015-08-24 | 9999-12-31 |  29 | 1
 1564 | 2015-08-24 | 9999-12-31 |  40 | 1
 1564 | 2015-08-24 | 9999-12-31 |   2 | 4
 1564 | 2015-08-24 | 9999-12-31 |   0 | 116
 1564 | 2015-08-24 | 9999-12-31 |   1 | 1425
 1564 | 2015-08-24 | 9999-12-31 |  11 | zaortk
 1564 | 2015-08-24 | 9999-12-31 |  23 | 123
(11 rows)
 
db=# SELECT * FROM clife WHERE cid='303' AND now() BETWEEN dton AND dtoff AND value<>'0';
 cid |    dton    |   dtoff    | tid |      value
-----+------------+------------+-----+------------------
 303 | 2011-04-13 | 9999-12-31 |   2 | 4
 303 | 2011-04-19 | 9999-12-31 |   0 | 74
 303 | 2015-07-01 | 9999-12-31 |   1 | 1425
 303 | 2015-11-18 | 9999-12-31 |  26 | 10000
 303 | 2011-04-13 | 9999-12-31 |  11 | rtkfri
 303 | 2011-04-13 | 9999-12-31 |  23 | 123
 303 | 2011-04-13 | 9999-12-31 |  15 | 83.234.161.77/32
 303 | 2011-04-13 | 9999-12-31 |  27 | gw::em3
 303 | 2012-07-26 | 9999-12-31 |  28 | 381
 303 | 2012-05-02 | 9999-12-31 |  41 | 1
(10 rows)
 
db=# SELECT * FROM clife WHERE cid='1155' AND now() BETWEEN dton AND dtoff AND value<>'0';
 cid  |    dton    |   dtoff    | tid |      value
------+------------+------------+-----+-----------------
 1155 | 2013-11-08 | 9999-12-31 |   2 | 4
 1155 | 2013-11-08 | 9999-12-31 |   0 | 82
 1155 | 2014-05-01 | 9999-12-31 |  26 | 300
 1155 | 2013-11-08 | 9999-12-31 |  11 | zaharovav
 1155 | 2013-11-08 | 9999-12-31 |  23 | 123
 1155 | 2013-11-08 | 9999-12-31 |  15 | 172.16.40.57/32
 1155 | 2013-11-08 | 9999-12-31 |  27 | gw::em4
 1155 | 2013-11-11 | 9999-12-31 |  28 | 1332
 1155 | 2013-11-08 | 9999-12-31 |  29 | 1
(9 rows)

dulog:
Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT * FROM dylog WHERE cid='1425' AND dt='2015-11-25' AND cost<>'0';
     dt     | sid | cid  | cnt | qnt |   cost
------------+-----+------+-----+-----+----------
 2015-11-25 |  25 | 1425 |   1 |   1 | 1095140
 2015-11-25 |   2 | 1425 |   1 |   1 | 10951403
 2015-11-25 |  20 | 1425 |   1 |   1 | 1095140
(3 rows)
 
db=# SELECT * FROM dylog WHERE cid='1564' AND dt='2015-11-25' AND cost<>'0';
     dt     | sid | cid  | cnt | qnt |   cost
------------+-----+------+-----+-----+----------
 2015-11-25 |  25 | 1564 |   1 |   1 | 1095140
 2015-11-25 |   2 | 1564 |   1 |   1 | 10951403
 2015-11-25 |  20 | 1564 |   1 |   1 | 1095140
(3 rows)
 
 
db=# SELECT * FROM dylog WHERE cid='303' AND dt='2015-11-25' AND cost<>'0';
     dt     | sid | cid | cnt | qnt |   cost
------------+-----+-----+-----+-----+----------
 2015-11-25 |  37 | 303 |   1 |   1 | 1314168
 2015-11-25 |   2 | 303 |   1 |   1 | 27378508
(2 rows)
 
 
db=# SELECT * FROM dylog WHERE cid='1155' AND dt='2015-11-25' AND cost<>'0';
     dt     | sid | cid  | cnt | qnt |  cost
------------+-----+------+-----+-----+---------
 2015-11-25 |  25 | 1155 |   1 |   1 | 1095140
 2015-11-25 |   2 | 1155 |   1 |   1 | 3285421
(2 rows)

Данные для моего вопроса, требуются не все, которые я дал.

Из всего, что я дал, нужно выбрать cid,sid,cost (ЛС, id услуги и стоимость соответственно) из таблицы dylog, за текущую дату и стоимостью не равную нулю. Это нужно, для выгрузки начисления в 1С. С этим проблем не возникает, например:

Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT cid,sid,cost FROM dylog WHERE cid='1425' AND dt='2015-11-25' AND cost<>'0';
 cid  | sid |   cost
------+-----+----------
 1425 |  25 | 1095140
 1425 |   2 | 10951403
 1425 |  20 | 1095140
(3 rows)

А дальше начинается интересней. Дело в том, что, один лицевой счет может являться плательщиком у другого. В моем примере, это ЛС 1425, который является плательщиком у счетов 1564 и 303. Об этом свидетельствует запись в выборке из таблицы clife:
Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT * FROM clife WHERE cid='1564' AND tid='1' AND now() BETWEEN dton AND dtoff AND value<>'0';
 cid  |    dton    |   dtoff    | tid | value
------+------------+------------+-----+-------
 1564 | 2015-08-24 | 9999-12-31 |   1 | 1425
(1 запись)

Т.е. за ЛС 1564 платит ЛС 1425. Другими словами, списание плат, за услуги, которые предоставляются для ЛС 1564, производится с ЛС 1425.

Теперь самое сложное, верно сформулировать, что мне нужно...

А нужно следующие:
Выбрать начисления из таблицы dylog для каждого ЛС. Но при этом, если у данного ЛС (например 1564), плательщиком является другой ЛС (1425), то в результате выборке указывать ЛС плательщика. Пример вывода:

Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT cid,sid,cost FROM dylog ..............;
 cid  | sid |   cost
------+-----+----------
 1425 |  25 | 1095140
 1425 |   2 | 10951403
 1425 |  20 | 1095140
 1425 |  37 | 1314168
 1425 |   2 | 27378508
 1425 |  25 | 1095140
 1425 |   2 | 10951403
 1425 |  20 | 1095140
 1155 |  25 | 1095140
 1155 |   2 | 3285421

В этой выборке, первые 3 строки, для ЛС 1425, следующие 2, для ЛС 303, далее 3 строки для ЛС 1564 и последние 3 ЛС 1155.
В выборке не указаны счета 303 и 1564, т.к. плательщиком у них является 1425, соответственно он и указан.
Так же, идеально было бы, если бы, одинаковые sid, для одного и того же ЛС в выборке, группировались, а cost суммировался. Т.е получился бы примерно следующий вывод:
Используйте данную ссылку, что бы развернуть или свернуть блок текста
db=# SELECT cid,sid,cost FROM dylog ..............;
 cid  | sid |   cost
------+-----+----------
 1425 |  25 | 2190280
 1425 |   2 | 32854209
 1425 |  20 | 2190280
 1425 |  37 | 1314168
 1155 |  25 | 1095140
 1155 |   2 | 3285421

Тут мы сгруппировали sid'ы 2, 20 и 25, а их cost суммировали.

Надеюсь, я все понятно расписал. Но если возникнут вопросы, то задавайте.

‹ Упрощение запроса Использование функции to_tsvector ›

Back to top

(С) Виктор Вислобоков, 2008-2023