ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Не простая выборка.
17 сообщений из 17, страница 1 из 1
Не простая выборка.
    #39113744
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
Добрый день.
Имеется самописная биллинг система, которая досталась мне по наследству. И сейчас требуется сделать выборку для 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:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
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)


dylog:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
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С. С этим проблем не возникает, например:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
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:
 
Код: sql
1.
2.
3.
4.
5.
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), то в результате выборке указывать ЛС плательщика. Пример вывода:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 суммировался. Т.е получился бы примерно следующий вывод:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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 суммировали.

Надеюсь, я все понятно расписал. Но если возникнут вопросы, то задавайте.
...
Рейтинг: 0 / 0
26.11.2015, 09:29
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113794
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
KsenZ,
...
Рейтинг: 0 / 0
26.11.2015, 10:29
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113797
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
KsenZ,

(Прошу прощения за пустое сообщение.)

Какова максимальная глубина наследования счетов?

В вашем примере может быть, что:
- 1564 платит за 5678
- 1425 платит за 1564 и, по наследству, еще и за 5678

?
...
Рейтинг: 0 / 0
26.11.2015, 10:31
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113803
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
vyegorovKsenZ,

(Прошу прощения за пустое сообщение.)

Какова максимальная глубина наследования счетов?

В вашем примере может быть, что:
- 1564 платит за 5678
- 1425 платит за 1564 и, по наследству, еще и за 5678

?

Нет. 1425 платит за себя, и может платить еще за несколько счетов, например за 1564 и 303. Но на этом все, ни 1564 ни 303 не могут платить за кого то еще.
...
Рейтинг: 0 / 0
26.11.2015, 10:40
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113820
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
KsenZНет. 1425 платит за себя, и может платить еще за несколько счетов, например за 1564 и 303. Но на этом все, ни 1564 ни 303 не могут платить за кого то еще.Вы не можете присоединить таблицу саму к себе по LEFT JOIN и в списке полей секции SELECT заменить "пустышки" из левоприсоединенной копии таблицы на данные лицевых счетов из основной?
...
Рейтинг: 0 / 0
26.11.2015, 10:58
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113822
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
KsenZ,

если что, читать про [LEFT | RIGHT] outer join, про coalesce / case
...
Рейтинг: 0 / 0
26.11.2015, 10:59
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113840
Фотография Владимир Лазурко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Лазурко
Участник
KsenZНет. 1425 платит за себя, и может платить еще за несколько счетов, например за 1564 и 303. Но на этом все, ни 1564 ни 303 не могут платить за кого то еще.То есть 1425 головной контрагент. Но эти связи где-то должны быть прописаны - может есть другая таблица, содержащая эти связи? И уже от этого плясать.
Ну или из 1С тогда забирать данные справочника Контрагенты 1С (там уже эта связь контрагент-головной контрагент скорее всего прописана) и по этой связи из 1С связывать оплаты.
...
Рейтинг: 0 / 0
26.11.2015, 11:25
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113861
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
Владимир ЛазуркоKsenZНет. 1425 платит за себя, и может платить еще за несколько счетов, например за 1564 и 303. Но на этом все, ни 1564 ни 303 не могут платить за кого то еще.То есть 1425 головной контрагент. Но эти связи где-то должны быть прописаны - может есть другая таблица, содержащая эти связи? И уже от этого плясать.
Ну или из 1С тогда забирать данные справочника Контрагенты 1С (там уже эта связь контрагент-головной контрагент скорее всего прописана) и по этой связи из 1С связывать оплаты.

Да, как я писал выше, эти связи видно в таблице clife. Например:
Код: sql
1.
2.
3.
4.
5.
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 запись)



Т.е. cid - это текущий ЛС, tid - идентификатор, значение 1, как раз, и означает, что он не сам платит, value - соответственно ЛС плательщика.
У ЛС, которые платять сами за себя, такой строки с tid=1 в таблице нет.
...
Рейтинг: 0 / 0
26.11.2015, 11:46
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113907
Фотография Владимир Лазурко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Лазурко
Участник
KsenZ,

ок. А я говорю о связях, которы еидентифицируют, за кого 1425 может платить, а за кого нет.
...
Рейтинг: 0 / 0
26.11.2015, 12:28
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113931
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
KsenZ,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select t.cit, t.sid, sum(t.cost) as cost
from
(select CASE WHEN c.tid = 1 THEN c.value
            ELSE d.cit
        END as cit,
        d.sid,
        d.cost
 from dylog d, clife c
 where d.cit = c.cit)t
group by t.cit, t.sid


это надо?
...
Рейтинг: 0 / 0
26.11.2015, 12:50
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113934
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
Владимир ЛазуркоKsenZ,

ок. А я говорю о связях, которы еидентифицируют, за кого 1425 может платить, а за кого нет.

Это делается в обратном направлении, если так можно сказать.
1564 смотрит в таблице clife, есть ли у него tid=1, т.е. сам ли он оплачивает услуги, или нет. Если tid имеется, то смотрит в value значение ЛС плательщика. И за услуго, списываются средства с ЛС плательщика.
...
Рейтинг: 0 / 0
26.11.2015, 12:51
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39113937
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
KsenZУ ЛС, которые платять сами за себя, такой строки с tid=1 в таблице нет.
то есть так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select t.cit, t.sid, sum(t.cost) as cost
from
(select COALESCE(c.value,d.cit) as cit,
        d.sid,
        d.cost
 from dylog d
  left outer join clife c on (c.cit = d.cit))t
group by t.cit, t.sid
...
Рейтинг: 0 / 0
26.11.2015, 12:53
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39114352
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
roadsterKsenZУ ЛС, которые платять сами за себя, такой строки с tid=1 в таблице нет.
то есть так
Код: sql
1.
2.
3.
select t.cit, t.sid, sum(t.cost) as cost
from
.......



ERROR: COALESCE types text and integer cannot be matched
...
Рейтинг: 0 / 0
26.11.2015, 19:29
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39114372
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
KsenZERROR: COALESCE types text and integer cannot be matchedпочитать про преобразование типов: ::, cast, to_number
...
Рейтинг: 0 / 0
26.11.2015, 20:09
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39114642
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
KsenZroadsterпропущено...

то есть так
Код: sql
1.
2.
3.
select t.cit, t.sid, sum(t.cost) as cost
from
.......




ERROR: COALESCE types text and integer cannot be matchedда уж...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select t.cit, t.sid, sum(t.cost) as cost
from
(select COALESCE(c.value,d.cit) as cit,
        d.sid,
        CAST(d.cost AS NUMBER) AS cost
 from dylog d
  left outer join clife c on (c.cit = d.cit))t
group by t.cit, t.sid


примерно как-то так.

кстати, я тут подумал...
а вы не боитесь выставить суммы за весь период времени, по которому есть данные? думаю плтельщики будут не в восторге.
ЗЫ а судя по вашим вопросам вы таки можете выдать ошеломляющие цифры.
...
Рейтинг: 0 / 0
27.11.2015, 10:11
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39114663
KsenZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KsenZ
Гость
roadsterKsenZпропущено...


ERROR: COALESCE types text and integer cannot be matchedда уж...
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select t.cit, t.sid, sum(t.cost) as cost
from
(select COALESCE(c.value,d.cit) as cit,
        d.sid,
        CAST(d.cost AS NUMBER) AS cost
 from dylog d
  left outer join clife c on (c.cit = d.cit))t
group by t.cit, t.sid


примерно как-то так.

кстати, я тут подумал...
а вы не боитесь выставить суммы за весь период времени, по которому есть данные? думаю плтельщики будут не в восторге.
ЗЫ а судя по вашим вопросам вы таки можете выдать ошеломляющие цифры.

Такая же ошибка. Преобразовать нужно не cost, а value , т.к.у этого столбца тип text. Побывал сделать CAST(c.value AS NUMBER) но эффекта это не дало.
ERROR: type "number" does not exist
LINE 1: ...st) as cost from (select COALESCE(CAST(c.value AS NUMBER),d...

По поводу того, что насчитаю много, то этого не будет, т.к. выборка будет только за текущий день, нечто вроде AND dt='2015-11-27'
...
Рейтинг: 0 / 0
27.11.2015, 10:38
| Ответить | Цитировать | Написать  
Не простая выборка.
    #39114746
Фотография roadster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
roadster
Участник
KsenZПобывал сделать CAST(c.value AS NUMBER) но эффекта это не дало.NUMBER не постгресный тип, преобразуйте к нужному, что там есть INTEGER, FLOAT или ещё чего.
в чём проблема?
...
Рейтинг: 0 / 0
27.11.2015, 11:46
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Не простая выборка.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39113820&tid=1997606]:
 0ms
get settings:
 8ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 234ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 75ms
get tp. blocked users:
 2ms
others: 245ms
total:  610ms
созд. / загр.: 610ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]