У програмі по нерухомості Квартал ПРО вбудований конструктор даних. Для його виклику скористайтеся меню «Сервіс->Конструктор даних». Перед Вами відкриється вікно конструктора, розділене на дві частини. У верхню частину можна вносити запити мовою SQL. Введіть (вставте) текст запиту у верхнє вікно і натисніть зелену кнопку Старт (праворуч). У нижній частині отримаєте результат. Запит можна зберігати для використання в подальшому. Результат можна роздрукувати або експортувати в різні формати. Запит можна зберігати, вказавши йому зручну назву (ім'я), щоб не вставляти його щоразу.

 

Примітка. При вставці прикладів у вікно конструктора зірочки вставляти не потрібно.

 

Середня ціна по районах.

Як можна дізнатися середню ціну по районах міста?

*****************************************************************

SELECT AVG(A.PRICE), r.name from APART A
LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)
group by 2

*****************************************************************

 

Ще один варіант цього запиту, більш розширений. З українськими назвами стовпців:

*******************************************************************

SELECT AVG(A.PRICE) as "Середня ціна", r.name as "Район" from APART A
LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)

group by 2

*******************************************************************

 

Ще один варіант цього запиту, більш розширений. Середня ціна розбита не тільки по районах, але і по кількості кімнат. З українськими назвами стовпців.

*******************************************************************

SELECT AVG(A.PRICE) as "Середня ціна", r.name as "Район", a.ROOMS_Q as "Кільк. кімнат" from APART A
LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)

group by 2,3

*******************************************************************

 

Ще один варіант цього запиту, більш розширений. Середня ціна розбита не тільки по районах, але і по кількості кімнат. Виконано сортування по районах. З українськими назвами стовпців.

*******************************************************************

SELECT AVG(A.PRICE) as "Середня ціна", r.name as "Район", a.ROOMS_Q as "Кільк. кімнат" from APART A
LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)

group by 2,3
order by 2

*******************************************************************

 

 

Визначити середню ціну по кімнатам.

***********************************************************

SELECT AVG(A.PRICE) as "Середня ціна", a.ROOMS_Q as "Кільк. кімнат" from APART A group by 2

***********************************************************

 

Визначити середню ціну по районах з кількістю об'єктів у кожній групі.

***********************************************************

SELECT AVG(A.PRICE) as "Середняя ціна", r.name as "Район", a.ROOMS_Q as "Кільк. кімнат", COUNT(a.ID) AS "Кільк. об'єктів" from APART A
LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)
group by 2, 3

***********************************************************

 

Визначити середню ціну по районах тільки квартир. Будинки, новобудови, ділянки до розрахунку не беремо.

Це складніше.
1. Створюємо попередньо спеціальну процедуру в базі, яка допоможе нам у майбутньому. Тобто це потрібно зробити 1 раз. Відкрийте меню Сервіс/Виконати скрипт, вставте туди текст нижче між зірочками і натисніть Виконати.

**********************************************************

SET TERM ^ ;

create or alter procedure SP_TYPES_RECURSIVE (
    ANID integer,
    SELF integer,
    ID_SUPERTYPE integer)
returns (
    ID integer)
as
BEGIN

If (ID_SUPERTYPE = -1) Then
begin
  select id from types t where t.SUPERTYPE = :ID_SUPERTYPE into :ANID;
  ID_SUPERTYPE = -1;
end

IF (Self = 0) THEN
BEGIN
  ID = :AnID;
  SUSPEND;
END

FOR SELECT id FROM TYPES
WHERE parent = :anid INTO :ID
  DO begin
    SUSPEND;
    FOR SELECT id FROM SP_TYPES_RECURSIVE(:ID, 1, :ID_SUPERTYPE) INTO :ID
      DO SUSPEND;
  end

END^

SET TERM ; ^

**********************************************************

Вище - це скрипт створення процедури. Його потрібно виконати в програмі 1 раз. Виділіть код мишкою і натисніть на клавіатурі комбінацію Ctrl+C. Вставте курсор в редактор скриптів і натисніть Ctrl+V. Як виконати SQL-скрипт >>>.

Цей скрипт потрібно виконати 1 раз, оскільки він створить процедуру, яка назавжди залишиться в базі.

2. Як тільки скрипт буде успішно виконаний, відкрийте конструктор даних.

3. Для початку потрібно дізнатися «головний» ID типу квартир. Виконайте наступний код

4. У верхнє вікно вставте наступний код:

 

***********************************************************

SELECT
AVG(A.PRICE) as "Середня ціна",
r.name as "Район",
a.ROOMS_Q as "Кільк. кімнат",
tt1.NAME AS "Тип",
COUNT(a.ID) AS "Кільк. об'ектів"

from APART A

LEFT JOIN REGIONS R ON (r.ID = a.ID_REGION)
LEFT JOIN TYPES tt1 ON (tt1.ID = a.ID_TYPE)

where a.id_type in (SELECT t.id FROM TYPES t JOIN SP_TYPES_RECURSIVE (0, 0, 0) r ON t.id = r.id)
group by 2, 3, 4

***********************************************************

 

 

 

 

Статистика додавання об'єктів нерухомості за користувачами та датами.

Як можна дізнатися, скільки об'єктів додав агент за місяць?

*****************************************************************

SELECT COUNT(J.ID) as "Кількість", U.NAME as "Користувач"
FROM JOURNAL J

LEFT JOIN
USERS U ON (U.ID = J.UID)

WHERE
J.DATE_TIME >= '01.10.2008 00:00'
AND
J.DATE_TIME <= '01.11.2008 00:00'
AND
J.TABLE_NAME = 'Кв/Буд/Ново.'
AND
J.ACT = 'ДОДАВАННЯ'

GROUP BY 2

*****************************************************************

Ось результат: за жовтень місяць 2008 року по всіх користувачах і модулях будинку, квартири, новобудови

 

 

Статистика додавань та оновлень об'єктів нерухомості за користувачами та датами.

Як можна дізнатися, скільки об'єктів додав та оновив той чи інший користувач?

*****************************************************************

SELECT u.name as "Користувач",
(        select count(j1.id) from JOURNAL j1
WHERE
J1.DATE_TIME BETWEEN '01.10.2008 00:00' AND '01.11.2008 00:00'
AND
J1.TABLE_NAME = 'Оренда'
AND
J1.ACT = 'ДОДВАННЯ'
AND
j1.UID = u.ID
) as "Додано",
(        select count(j2.id) from JOURNAL j2
WHERE
J2.DATE_TIME BETWEEN '01.10.2008 00:00' AND '01.11.2008 00:00'
AND
J2.TABLE_NAME = 'Оренда'
AND
J2.ACT = 'ОНОВЛЕННЯ'
AND
j2.UID = u.ID
) as "Оновлено"
from users u

*****************************************************************

або так:

*****************************************************************

SELECT U.NAME as "Користувач",
sum (case when J.ACT = 'ДОДАВАННЯ' then 1 else 0 end) as "Додано",
sum (case when J.ACT = 'ОНОВЛЕННЯ' then 1 else 0 end) as "Оновлено"

FROM JOURNAL J
LEFT JOIN USERS U ON (U.ID = J.UID)

WHERE
J.DATE_TIME >= '01.10.2008 00:00'
AND
J.DATE_TIME <= '01.11.2008 00:00'
AND
J.TABLE_NAME = 'Оренда'

GROUP BY 1
ORDER BY 1
*****************************************************************

Ось результати: