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

Sql запросы

Posted Июнь 1st, 2011 by xigorx
in

Знатоки Sql, помогите пожалуйста написать запросы, а то без них курсовик никак не сдать, а свои мысли уже иссякли...

1. Тип(ы) снаряда, имеющий наибольшее значение отношения числа попаданий к
числу выстрелов. Упорядочить по названию типа.

2. Все пары различных бортовых номеров, принимавших участие только в одних
и тех же учениях. Упорядочить по первому номеру и по второму.

ВложениеРазмер
Структура бд55.78 kb
‹ В SELECT условие Нуждаюсь в вашей помощи ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Моё мнение как администратора ресурса:

On Июнь 1st, 2011 admin says:

Ресурс предназначен не для поиска добрых дядей, которые сделают за вас домашние задания и упражнения: здесь люди заняты серьёзным делом. Тратить своё время на тех, кто пишет курсовую, но не в состоянии хотя бы попытаться решить в общем-то несложную задачу не вижу смысла.

Пытался решить, притом не

On Июнь 1st, 2011 xigorx says:

Пытался решить, притом не однократно, результат один и тот же:"запрос не правильный, идите думайте".

Вы на бумажке решаете что ли?

On Июнь 1st, 2011 admin says:

Вы на бумажке решаете что ли? Поставьте СУБД себе на компьютер, выполните запросы, которые вы придумали. Покажите нам что придумали и что не получается - это будет предметный разговор вас как человека, который хочет чему-то научиться, а не просто хочет, чтобы за него всё сделал добрый дядя. Человеку, который что-то пытается сделать, хочет чему-то научиться не грех и помочь.

Субд поставлено, базы созданы

On Июнь 1st, 2011 xigorx says:

Субд поставлено, базы созданы и заполнены.

1

SELECT DISTINCT tip
FROM (( pushki INNER JOIN texnika ON texnika.id_pushki=pushki.id_pushki)
INNER JOIN rezultat ON rezultat.id_texniki=texnika.id_texniki)INNER JOIN snarydi ON snarydi.id_snaryda=pushki.id_snaryda 
WHERE popadaniy/vistrelov = (SELECT max(popadaniy/vistrelov) 
				FROM ( pushki INNER JOIN texnika ON texnika.id_pushki=pushki.id_pushki)
					INNER JOIN rezultat ON rezultat.id_texniki=texnika.id_texniki);

2

SELECT DISTINCT R1.id_texniki AS id_texniki1, R2.id_texniki AS id_texniki2
FROM rezultat AS R1, rezultat AS R2 
WHERE R1.DATA=R2.DATA AND R1.id_texniki < R2.id_texniki AND R1.id_texniki NOT IN (SELECT R3.id_texniki
FROM rezultat AS R3, rezultat AS R4 
WHERE R3.DATA=R4.DATA AND R3.id_texniki < R4.id_texniki AND (( R3.id_texniki=R1.id_texniki AND R4.id_texniki<>R2.id_texniki) OR(R3.id_texniki<>R1.id_texniki AND R4.id_texniki=R2.id_texniki)))
AND R2.id_texniki NOT IN (SELECT R3.id_texniki
FROM rezultat AS R3, rezultat AS R4 
WHERE R3.DATA=R4.DATA AND R3.id_texniki < R4.id_texniki AND (( R3.id_texniki=R1.id_texniki AND R4.id_texniki<>R2.id_texniki) OR(R3.id_texniki<>R1.id_texniki AND R4.id_texniki=R2.id_texniki)))
ORDER BY id_texniki1, id_texniki2;

Можете дамп таблиц с данными

On Июнь 2nd, 2011 admin says:

Можете дамп таблиц с данными прикрепить? Чтобы я у себя загнал.
Навскидку видно, что нигде нет ORDER BY и кроме того непонятно зачем DISTINCT, да и скобки вначале тоже лишнее.

В постгрессе я новичок, где

On Июнь 2nd, 2011 xigorx says:

В постгрессе я новичок, где взять дамп?
По поводу первого запроса мне сказали, что так как у меня написано, процент попаданий берётся только по одному учению, а надо за все бои.

не скопировал последнюю строку, там было order by tip

Задачу вам тоже сформировали

On Июнь 2nd, 2011 admin says:

Задачу вам тоже сформировали мутно, вот и уточнять приходится. Препод ваш видимо тоже не очень шарит. Я же говорю без данных всё-равно что вилами по воде водишь, надо данные и пробовать реально что получается.

Дамп можно программой
pg_dump имя_базы > dump.sql
сделать. Не знаю как у вас по дефолту формат настроен, нужен дамп в текстовом виде - если что ключи почитайте.

Сделал дамп -- -- PostgreSQL

On Июнь 3rd, 2011 xigorx says:

Сделал дамп

--
-- PostgreSQL database dump
--
 
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
 
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--
 
CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
 
 
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
 
SET search_path = public, pg_catalog;
 
SET default_tablespace = '';
 
SET default_with_oids = false;
 
--
-- Name: mesto; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE mesto (
    id_mesta smallint NOT NULL,
    koordinata character(20) NOT NULL,
    nazvanie text NOT NULL
);
 
 
ALTER TABLE public.mesto OWNER TO postgres;
 
--
-- Name: pushki; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE pushki (
    nazvanie character(20),
    kalibr smallint NOT NULL,
    skorost numeric(4,2) NOT NULL,
    id_snaryda smallint NOT NULL,
    id_pushki smallint NOT NULL,
    CONSTRAINT pushki_kalibr_check CHECK (((kalibr >= 0) AND (kalibr <= 300))),
    CONSTRAINT pushki_skorost_check CHECK ((skorost > (0)::numeric))
);
 
 
ALTER TABLE public.pushki OWNER TO postgres;
 
--
-- Name: rezultat; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE rezultat (
    id_texniki smallint NOT NULL,
    popadaniy smallint NOT NULL,
    vistrelov smallint NOT NULL,
    povrezhdeniy smallint NOT NULL,
    DATA date NOT NULL,
    CONSTRAINT rezultat_check CHECK (((popadaniy >= 0) AND (popadaniy <= vistrelov))),
    CONSTRAINT rezultat_povrezhdeniy_check CHECK ((povrezhdeniy > 0)),
    CONSTRAINT rezultat_vistrelov_check CHECK ((vistrelov > 0))
);
 
 
ALTER TABLE public.rezultat OWNER TO postgres;
 
--
-- Name: snarydi; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE snarydi (
    id_snaryda smallint NOT NULL,
    broneprobivaemost numeric(4,2) NOT NULL,
    tip character(20) NOT NULL,
    CONSTRAINT snarydi_broneprobivaemost_check CHECK ((broneprobivaemost > (0)::numeric)),
    CONSTRAINT snarydi_tip_check CHECK ((tip = ANY (ARRAY['Бронебойный'::bpchar, 'Кумулятивный'::bpchar, 'Фугас'::bpchar])))
);
 
 
ALTER TABLE public.snarydi OWNER TO postgres;
 
--
-- Name: texnika; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE texnika (
    id_texniki smallint NOT NULL,
    id_pushki smallint NOT NULL,
    dvigatel character(20) NOT NULL,
    xodovaya character(20) NOT NULL,
    bashnya character(20) NOT NULL
);
 
 
ALTER TABLE public.texnika OWNER TO postgres;
 
--
-- Name: ucheniya; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
 
CREATE TABLE ucheniya (
    DATA date NOT NULL,
    id_mesta smallint NOT NULL
);
 
 
ALTER TABLE public.ucheniya OWNER TO postgres;
 
--
-- Data for Name: mesto; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY mesto (id_mesta, koordinata, nazvanie) FROM stdin;
1	57/49/00|28/20/00   	Pskov
2	55/45/20|37/37/03   	Moskow
3	51/40/18|39/12/38   	Voronezh
4	51/43/00|36/11/00   	Kursk
\.
 
 
--
-- Data for Name: pushki; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY pushki (nazvanie, kalibr, skorost, id_snaryda, id_pushki) FROM stdin;
ЗИС-7               	107	10.20	1	1
МЛ                  	85	11.55	1	2
МЛ-2                	85	11.55	2	3
С-70                	105	4.40	3	4
Ф-32                	76	15.00	1	5
Л-10                	76	13.55	2	6
\.
 
 
--
-- Data for Name: rezultat; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY rezultat (id_texniki, popadaniy, vistrelov, povrezhdeniy, DATA) FROM stdin;
43	10	16	25	2008-06-05
13	20	23	11	2008-06-05
22	15	19	15	2008-06-05
43	5	9	11	2009-04-12
55	3	4	20	2009-04-12
67	15	18	4	2009-04-12
76	11	11	6	2009-04-12
36	2	10	5	2009-04-12
29	11	13	4	2009-04-12
43	12	16	1	2007-11-05
58	4	11	28	2007-11-05
36	6	7	2	2007-11-05
43	13	13	6	2010-10-13
58	6	8	19	2010-10-13
55	10	14	5	2010-10-13
43	6	8	4	2011-01-01
13	8	11	2	2011-01-01
22	5	6	7	2011-01-01
55	12	12	7	2011-01-01
67	15	17	15	2011-01-01
76	10	10	12	2011-01-01
29	7	9	11	2011-01-01
\.
 
 
--
-- Data for Name: snarydi; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY snarydi (id_snaryda, broneprobivaemost, tip) FROM stdin;
1	52.25	Бронебойный         
2	88.20	Кумулятивный        
3	63.50	Фугас               
\.
 
 
--
-- Data for Name: texnika; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY texnika (id_texniki, id_pushki, dvigatel, xodovaya, bashnya) FROM stdin;
34	1	В-2-34              	Т-46                	Т-34                
43	2	В-5                 	Т-43                	Т-43                
13	3	В-2ИС               	обр. 710            	ИС-4м               
22	4	В-2-34М             	КВ-2                	Кв-2                
55	5	М-17Ф               	Т-43М               	Т-43-100            
31	6	В-2-34              	Т-46                	Т-34                
67	1	В-2-34              	Т-46                	Т-34                
76	2	В-5                 	Т-43                	Т-43                
36	3	В-2ИС               	обр. 710            	ИС-4м               
29	4	В-2-34М             	КВ-2                	Кв-2                
58	5	М-17Ф               	Т-43М               	Т-43-100            
\.
 
 
--
-- Data for Name: ucheniya; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
COPY ucheniya (DATA, id_mesta) FROM stdin;
2008-06-05	1
2009-04-12	1
2007-11-05	2
2010-10-13	3
2011-01-01	4
\.
 
 
--
-- Name: mesto_koordinata_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY mesto
    ADD CONSTRAINT mesto_koordinata_key UNIQUE (koordinata);
 
 
--
-- Name: mesto_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY mesto
    ADD CONSTRAINT mesto_name_key UNIQUE (nazvanie);
 
 
--
-- Name: mesto_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY mesto
    ADD CONSTRAINT mesto_pkey PRIMARY KEY (id_mesta);
 
 
--
-- Name: pushki_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY pushki
    ADD CONSTRAINT pushki_name_key UNIQUE (nazvanie);
 
 
--
-- Name: pushki_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY pushki
    ADD CONSTRAINT pushki_pkey PRIMARY KEY (id_pushki);
 
 
--
-- Name: rezultat_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY rezultat
    ADD CONSTRAINT rezultat_pkey PRIMARY KEY (id_texniki, DATA);
 
 
--
-- Name: snarydi_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY snarydi
    ADD CONSTRAINT snarydi_pkey PRIMARY KEY (id_snaryda);
 
 
--
-- Name: snarydi_tip_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY snarydi
    ADD CONSTRAINT snarydi_tip_key UNIQUE (tip);
 
 
--
-- Name: texnika_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY texnika
    ADD CONSTRAINT texnika_pkey PRIMARY KEY (id_texniki);
 
 
--
-- Name: ucheniya_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--
 
ALTER TABLE ONLY ucheniya
    ADD CONSTRAINT ucheniya_pkey PRIMARY KEY (DATA);
 
 
--
-- Name: pushki_id_snaryda_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY pushki
    ADD CONSTRAINT pushki_id_snaryda_fkey FOREIGN KEY (id_snaryda) REFERENCES snarydi(id_snaryda);
 
 
--
-- Name: rezultat_data_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY rezultat
    ADD CONSTRAINT rezultat_data_fkey FOREIGN KEY (DATA) REFERENCES ucheniya(DATA);
 
 
--
-- Name: rezultat_id_texniki_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY rezultat
    ADD CONSTRAINT rezultat_id_texniki_fkey FOREIGN KEY (id_texniki) REFERENCES texnika(id_texniki);
 
 
--
-- Name: texnika_id_pushki_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY texnika
    ADD CONSTRAINT texnika_id_pushki_fkey FOREIGN KEY (id_pushki) REFERENCES pushki(id_pushki);
 
 
--
-- Name: ucheniya_id_mesta_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY ucheniya
    ADD CONSTRAINT ucheniya_id_mesta_fkey FOREIGN KEY (id_mesta) REFERENCES mesto(id_mesta);
 
 
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
 
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
 
 
--
-- PostgreSQL database dump complete
--

Первый запрос, я бы сделал

On Июнь 4th, 2011 admin says:

Первый запрос, я бы сделал так. Не знаю насколько изящно это, но работает:

SELECT tmp1.tip FROM
(SELECT max(rezultat.popadaniy*10/rezultat.vistrelov) AS otn, ucheniya.data,snarydi.tip
FROM rezultat
INNER JOIN ucheniya ON rezultat.data=ucheniya.data
INNER JOIN texnika ON texnika.id_texniki=rezultat.id_texniki
INNER JOIN pushki ON pushki.id_pushki=texnika.id_pushki
INNER JOIN snarydi ON snarydi.id_snaryda=pushki.id_snaryda
GROUP BY ucheniya.data,snarydi.tip) AS tmp1
INNER JOIN
(SELECT max(rezultat.popadaniy*10/rezultat.vistrelov) AS otn, rezultat.data
FROM rezultat
GROUP BY data) AS tmp2
ON tmp1.otn=tmp2.otn AND tmp1.data=tmp2.data
ORDER BY tmp1.tip;

Пояснения (если нужны, конечно). Первая виртуальная таблица tmp1 делается с таким количество INNER JOIN, чтобы вытащить название типа и получить таблицу вида:

 otn |    data    |         tip          
-----+------------+----------------------
   8 | 2007-11-05 | Кумулятивный        
   7 | 2007-11-05 | Бронебойный         
  10 | 2009-04-12 | Бронебойный         
   8 | 2009-04-12 | Фугас               
   7 | 2011-01-01 | Кумулятивный        
   2 | 2009-04-12 | Кумулятивный        
   8 | 2008-06-05 | Кумулятивный        
  10 | 2010-10-13 | Бронебойный         
   6 | 2008-06-05 | Бронебойный         
   7 | 2008-06-05 | Фугас               
  10 | 2011-01-01 | Бронебойный         
   8 | 2011-01-01 | Фугас

где как мы видим даны даты учений, типы снарядов, которые участвовали в учениях и максимальное отношение попаданий к числу выстрелов для данных типов снарядов умноженное на 10. На 10 умножаем потому, что результатом деления является число меньше 1 и если не умножать то нет разницы что делили скажем 5/10 или 8/10 - всё равно целое будет 0. Можно, конечно, заморачиваться с приведением типов, но мне было лень: если нужно делайте сами, а я просто умножил на 10.

Вторая виртуальная таблица tmp2 не содержит типов снарядов, зато содержит нужное нам отношение попаданий к числу выстрелов наибольшее за всю дату учений без привязки к типу снарядов:

 otn |    data    
-----+------------
  10 | 2011-01-01
  10 | 2009-04-12
  10 | 2010-10-13
   8 | 2008-06-05
   8 | 2007-11-05

Всё что осталось сделать: соединить обе таблицы и получить нужный нам тип, упорядочив его по названию, что и сделано.

Спасибо, только вот как

On Июнь 5th, 2011 xigorx says:

Спасибо, только вот как сделать чтоб выводил только один тип снаряда или 2 (3), если у них процент попаданий одинаковый. LIMIT'ом как я понимаю тут не отделаешься..

Я не пойму тогда, вам же

On Июнь 6th, 2011 admin says:

Я не пойму тогда, вам же сказали для каждого учения вывести? Вот для каждого и выводится. Не нужны дубли в результате - DISTINCT добавьте.

У вас нет одинаковых процентов попаданий, если посмотрите таблицу, но в принципе и это можно сделать, (предположительно - сейчас некогда проверять) если убрать max из первой виртуальной таблицы.

Идею понял, первый запрос

On Июнь 10th, 2011 xigorx says:

Идею понял, первый запрос сделал

CREATE VIEW R3 AS (
SELECT pushki.id_snaryda, sum(popadaniy*1000)/sum(vistrelov) AS otnoshehie
FROM ( pushki INNER JOIN texnika ON texnika.id_pushki=pushki.id_pushki)
INNER JOIN rezultat ON rezultat.id_texniki=texnika.id_texniki
GROUP BY pushki.id_snaryda);
 
SELECT tip
FROM R3 INNER JOIN snarydi ON snarydi.id_snaryda=R3.id_snaryda
WHERE otnoshehie = (SELECT max(otnoshehie)
FROM R3);

Помогите пожалуйста со вторым.

SELECT a.id, b.id FROM

On Июнь 17th, 2011 Skiv-mag says:

SELECT a.id, b.id 
FROM
    (SELECT id_texniki AS id, date FROM rezultat) AS a
    INNER JOIN 
    (SELECT id_texniki AS id, date FROM rezultat) AS b
    ON a.id != b.id
        AND a.date = b.date
ORDER BY 1,2

Не знаю что там за бортовые номера, структуре не указано, так что вытягивает пары айдишников техники. Называется это дело кортезианское произведение (cross join, декартово произведение, гуглить по этим терминам), его задает неравенство в условии обединения, дальше мы уже просто ограничиваем совпадающими датами и сортируем.
P.S
Мой уважаемый гуру, учитель и тимлид прибил бы меня за такие названия столбцов. Хотите писать английскими буквами - учите английский, не знаете - гугл транслэйт под рукой. Это элементарная этика кодинга.

Боттовые номера это и есть

On Июнь 25th, 2011 xigorx says:

Боттовые номера это и есть айди техники.
В результате он выведет все пары бортовых номеров, которые вместе были на учениях, а надо только тех, которые всегда вместе были на учениях.
То есть:
-------------------------------
|бортовой номер|дата |
|2 |21.12.89|
|5 |21.12.89|
|3 |21.12.89|
|3 |22.12.89|
|4 |22.12.89|
|5 |22.12.89|
-------------------------------
должен выдать пару 3 и 5 и всё.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

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