LINUX.ORG.RU

Подкорректируйте SQL-запрос

 , ,


1

1

Подкорректируйте запрос с JOIN'ами.

Вот такая простенькая база данных:

CREATE TABLE "house" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"location" VARCHAR(255) NOT NULL)

CREATE TABLE "contract" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"house_id" INTEGER NOT NULL, 
FOREIGN KEY ("house_id") REFERENCES "house" ("id"))

CREATE INDEX "contract_house_id" ON "contract" ("house_id")

CREATE TABLE "accrual" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"datestamp" DATE NOT NULL, 
"value" REAL NOT NULL, 
"contract_id" INTEGER NOT NULL, 
FOREIGN KEY ("contract_id") REFERENCES "contract" ("id"))

CREATE INDEX "accrual_contract_id" ON "accrual" ("contract_id")

CREATE TABLE "payment" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"datestamp" DATE NOT NULL, 
"value" REAL NOT NULL, 
"contract_id" INTEGER NOT NULL, 
FOREIGN KEY ("contract_id") REFERENCES "contract" ("id"))

CREATE INDEX "payment_contract_id" ON "payment" ("contract_id")

Вот такой запрос к базе данных отрабатывает отлично:

SELECT "t1"."id", "t1"."name", "t1"."location", 
  Count(Distinct("t2"."id")) AS ccount, 
  Sum("t3"."value") AS all_payments 
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "payment" AS t3 ON ("t2"."id" = "t3"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location"

А здесь начинаются проблемы. При попытке указать в запросе еще один join - то данные начинают дублироваться и не правильно суммируются, результат Sum(«t4».«value») AS all_accruals завышен в несколько раз.

SELECT "t1"."id", "t1"."name", "t1"."location", 
  Count(Distinct("t2"."id")) AS ccount, 
  Sum("t3"."value") AS all_payments, 
  Sum("t4"."value") AS all_accruals 
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "payment" AS t3 ON ("t2"."id" = "t3"."contract_id") 
LEFT OUTER JOIN "accrual" AS t4 ON ("t2"."id" = "t4"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location"

З.ы. Как правильно составить SQL-запрос чтобы получить необходимые данные: правильное суммирование payment и accrual к каждой записи.

SQL сгенерировался через ORM, вот вся БД:

# encoding: utf-8
# dependencies:
# pip install peewee

from peewee import SqliteDatabase, Model, CharField, DateField, ForeignKeyField,\
                   FloatField, fn, SelectQuery, JOIN_LEFT_OUTER
import datetime
from random import randint

db = SqliteDatabase('test_pw.db')

class House(Model):
   
    name = CharField()
    location = CharField()
    class Meta:
        datadb = db
 
class Contract(Model):
   
    name = CharField()
    house = ForeignKeyField(House, related_name='contracts')
    class Meta:
        datadb = db
 
class Payment(Model):
   
    datestamp = DateField()
    value = FloatField()
    contract = ForeignKeyField(Contract, related_name='payments')
    class Meta:
        datadb = db
 
class Accrual(Model):
   
    datestamp = DateField()
    value = FloatField()
    contract = ForeignKeyField(Contract, related_name='accruals')
    class Meta:
        datadb = db
 
 
def fill_db():
    models = [House, Contract, Accrual, Payment]
    db.drop_tables(models, safe=True)
    db.create_tables(models)
    
#     for i in models:
#         print i.sqlall()
    
    houses = [{'name': u'TestHouse', 'location': 'Yellow street'},
              {'name': u'GreenHouse', 'location': 'Green street'},
              {'name': u'OpenHouse', 'location': 'Blue street'},]
    
    contracts = [{'name': u'Contract01', 'house': 1},
                 {'name': u'Contract02', 'house': 1},
                 {'name': u'Contract03', 'house': 1},
                 {'name': u'Contract04', 'house': 2},
                 {'name': u'Contract05', 'house': 2},
                 {'name': u'Contract06', 'house': 2},
                 {'name': u'Contract07', 'house': 2},
                 {'name': u'Contract08', 'house': 3},
                 {'name': u'Contract09', 'house': 3},]
    
    def generate_accruals(howmuch):
        temp_list = []
        base_date = datetime.datetime.today()
        
        for i in range (howmuch):
            data = {}
            data['datestamp'] = base_date - datetime.timedelta(days=i)
            data['value'] = randint(100, 500)
            data['contract'] = randint(1,len(contracts))
            temp_list.append(data)
        return temp_list
  
    accruals = generate_accruals(20)
    payments = generate_accruals(20)
  
    def summ_data(data):
        tmp = 0
        for item in data:
            tmp+=item['value']
        return tmp
    
    print 'Total accruals:', summ_data(accruals)
    print 'Total payments:', summ_data(payments)

    with db.transaction():
        House.insert_many(houses).execute()
        Contract.insert_many(contracts).execute()
        Payment.insert_many(payments).execute()
        Accrual.insert_many(accruals).execute() 

fill_db()

query = SelectQuery(House,
                    House,
                    fn.Count(fn.Distinct(Contract.id)).alias('ccount'),
                    fn.Sum(Payment.value).alias('all_payments'),
                    fn.Sum(Accrual.value).alias('all_accruals'),
                    )\
        .join(Contract, JOIN_LEFT_OUTER)\
        .join(Payment, JOIN_LEFT_OUTER)\
        .switch(Contract).join(Accrual)\
        .group_by(House)
        
# print query.sql()

for item in query:
    print item.name, item.location, item.ccount, item.all_payments , item.all_accruals


Результат:

Total accruals: 6291 # всего начислений Total payments: 5656

TestHouse Yellow street 3 5127.0 4065.0 # только в одной записи начислений 5127 GreenHouse Green street 4 6017.0 5525.0 OpenHouse Blue street 2 2171.0 1522.0

Всего начислений после запроса: 13315

★★★★★

Не проверял, в SQL не спец. Прозреваю надо как-то так, потому что в твоём варианте дублируется accural, т.к. после второго joina с payment contract.id повторяется много раз, и каждому из них (наверное, я не знаю) сопоставляется нечто из accural. В этом варианте ты сперва находишь только payments, потом только accurals, после чего объединяешь их обоих убирая пересечения, а уже потом объединяешь с house.

SELECT "t1"."id", "t1"."name", "t1"."location", 
  (Count(Distinct(all.contract_id)) AS ccount, 
  Sum(all.pay.value) AS all_payments, 
  Sum(all.acc.value) AS all_accruals 
from house as t1 left outer join (
(contract inner join payment on contract.id = payment.contract_id) as pay
outer join
(contract inner join accural on contract.id = accural.contract_id) as acc
on pay.contract_id = acc.contract_id) as all on house.id = all.house_id
GROUP BY "t1"."id", "t1"."name", "t1"."location"
Ivan_qrt ★★★★★ ()

З.ы. Как правильно составить SQL-запрос чтобы получить необходимые данные: правильное суммирование payment и accrual к каждой записи.

Делай два запроса. Один с payment второй с accrual. А то и три.

Вся это любовь к получению всего одним запросом до добра не доведет.

Если так хочется одним, то можно попытаться заменить join на subquery или uniuon из трех запросов

TEX ★★ ()

houses * contracts * payments * accruals </thread>

нафига было пилить payment и accrual отдельными сущностями? запиливай вместо них

class Operation(Model):
   
    datestamp = DateField()
    payment = FloatField()
    accrual = FloatField()
    contract = ForeignKeyField(Contract, related_name='payments')
    class Meta:
        datadb = db

например.

exception13 ★★★★★ ()

но совсем Ъ это:

CREATE TABLE "house" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"location" VARCHAR(255) NOT NULL);

CREATE TABLE "contract" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"name" VARCHAR(255) NOT NULL, 
"house_id" INTEGER NOT NULL, 
FOREIGN KEY ("house_id") REFERENCES "house" ("id"));

CREATE INDEX "contract_house_id" ON "contract" ("house_id");

CREATE TABLE "operation" (
"id" INTEGER NOT NULL PRIMARY KEY, 
"datestamp" DATE NOT NULL, 
"value" REAL NOT NULL, 
"contract_id" INTEGER NOT NULL, 
FOREIGN KEY ("contract_id") REFERENCES "contract" ("id"));

CREATE INDEX "operation_contract_id" ON "operation" ("contract_id");

-- operation summary
SELECT "t1"."id", "t1"."name", "t1"."location", 
  Count(Distinct("t2"."id")) AS ccount, 
  Sum(CASE WHEN "t3"."value" < 0 THEN "t3"."value" ELSE 0 END ) AS payment,
  Sum(CASE WHEN "t3"."value" > 0 THEN "t3"."value" ELSE 0 END ) AS accrual
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "operation" AS t3 ON ("t2"."id" = "t3"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location";

-- current balance
SELECT "t1"."id", "t1"."name", "t1"."location", 
  "t2"."id" AS contract_id, 
  Sum("t3"."value") AS balance
FROM "house" AS t1 
LEFT OUTER JOIN "contract" AS t2 ON ("t1"."id" = "t2"."house_id") 
LEFT OUTER JOIN "operation" AS t3 ON ("t2"."id" = "t3"."contract_id") 
GROUP BY "t1"."id", "t1"."name", "t1"."location", "t2"."id";
exception13 ★★★★★ ()
Ответ на: комментарий от TEX

Вся это любовь к получению всего одним запросом до добра не доведет.

если архитектура БД прямая, есть понимание основ реляционной алгебры и знания о фичах современного SQL то практически все выборки можно сделать одним запросом и это будет достаточно дешево.

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

Возможность != Необходимость.

Относительно ожидаемой дешевизны это еще бабка надвое сказала. Проекты имеют свойство развиваться, таблицы alter-ться, статистика меняться, не говоря уже об индексах.

С многочисленными join, без хинтования, в один прекрасный момент БД построит такой план что мало не покажется. А если возникнет задача переноса на другую БД - то многочисленные join по принципу «могу и буду» это прямой путь в бездну.

TEX ★★ ()
Последнее исправление: TEX (всего исправлений: 1)
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.