Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
Страницы: (2) [1] 2  ( Перейти к первому непрочитанному сообщению )  
Фильтр авторов:    показать 
  скрыть
  Ответ в темуСоздание новой темыСоздание опроса

> SQL выбор определенного значения или первое вхожде
AllesKlar  
 ۩  [x] Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 3691
Пользователь №: 38635
На форуме: 3 года, 5 месяцев, 27 дней
Карма: 195




Боевая задача перешла в область теории, т.к. нашли более красивое решение, но вопрос остался в голове :)

Итак, имеется денормализованная таблица (упрощенный вид):

CREATE TABLE public.objects (
id int4 NOT NULL DEFAULT nextval('objects_id_seq'::regclass),
object_id int8 NOT NULL,
title varchar(8192) NOT NULL,
title_lang varchar(8192) NULL
)



INSERT INTO public.objects 
(object_id, title, title_lang)
VALUES
(12345, 'TitleRU', 'ru'),
(
12345, 'TitleEN', 'EN'),
(
12345, 'TitleFR', 'fr'),
(
54321, 'DE Title', 'de'),
(
54321, 'EN Title', 'en')



Для каждого object_id может существовать несколько записей в таблице, по одной для пары title, title_lang.
Т.е. есть объект, у него есть титель, титель может быть на нескольких языках.

На входе имеем запрошенный язык.
Так же есть дефолтный язык.
Задача:
Для каждого объекта выбрать запись с запрошенным языком.
Если у объекта запрошенного языка нет, то выбрать запись с дефолтным языком.
Если и с дефолтным нет, то выбрать запись с первый встретившимся языком.
Так же отсортировать результат по тителю.

Дамп
select id, object_id, title, title_lang, * from objects where object_id in (12345, 54321)

user posted image

Входящие параметры в запрос:
- запрошенный язык: ru
- дефолтный язык: de

select DISTINCT on (o.object_id) o.object_id, 
case
when
olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)


Все хорошо, имеем результат
user posted image
Но, невозможно отсортировать по полю title т.к. имеем агрегатную функцию, и если сортировать, то сначала нужно сортировать по агрегируемому полю

/* ошибка */
select DISTINCT on (o.object_id) o.object_id,
case
when
olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)
order by title


Если же дать ему то, что он хочет, и сначала сортировать по агрегированому полю, то теряется сортировка по требуемому полю title
select DISTINCT on (o.object_id) o.object_id, 
case
when
olreq.title notnull then olreq.title
when oldef.title notnull then oldef.title
else o.title
end
as title
from objects o
left join objects olreq on (o.object_id = olreq.object_id and olreq.title_lang = 'ru')
left join objects oldef on (o.object_id = oldef.object_id and oldef.title_lang = 'de')
where o.object_id in (12345, 54321)
order by o.object_id, title


Есть идеи? :)
P.S. оборачивать запрос в другой запрос и сортировать уже результат запроса-врепера не подходит, т.к. помимо сортировки нужны еще LIMIT и OFFSET, и , если оборачивать другим SELECT * from ( SELECT .... ) то теряется весь смысл идеи, т.к. сильно просаживается производительность. А-ля прочитать сначала вообще всё, отсортировать, потом обрезать.


--------------------
[продано копирайтерам]
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




>> нашли более красивое решение

Сначала скажи, какое решение нашли, а потом видно будет, стоит ли тратить время.


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
AllesKlar  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 3691
Пользователь №: 38635
На форуме: 3 года, 5 месяцев, 27 дней
Карма: 195




Цитата (S.Chushkin @ 28.03.2017 - 06:20)
Сначала скажи, какое решение нашли, а потом видно будет, стоит ли тратить время.

Время вообще не стОит тратить.
Зачача в данном виде не актуальна. Тема создана для удовлетворения интереса и починки сломаных копий.



--------------------
[продано копирайтерам]
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




Цитата (AllesKlar @ 28.03.2017 - 03:11)
id int4 NOT NULL DEFAULT nextval('objects_id_seq'::regclass),

Судя по этой строчке, ты таки используешь Постгре. Но ты это не уточнил - люди будут искать решение в Мускуле, а возможности все-таки разные у них.

Задача прикольная, подумаю на досуге smile.gif Результат тут выложу.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




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

select * 
from
(
select distinct on (object_id) object_id, title, title_lang
from
(
select objects.*, 1 as lang_id from objects where title_lang = 'ru'
union
select
objects.*, 2 as lang_id from objects where title_lang = 'de'
union
select
objects.*, 3 as lang_id from objects where title_lang <> 'ru' and title_lang <> 'de'
) ini_sel
order by object_id, lang_id asc
) second_sel
order by title

И достаточно наглядный, плюс к тому, что весьма простой. И результат дает правильный, что весьма интересно :)

ПыСы. Надеюсь, что запрос понятен. Но если есть вопросы, то вэлкам!


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
AllesKlar  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 3691
Пользователь №: 38635
На форуме: 3 года, 5 месяцев, 27 дней
Карма: 195




sergeiss
Не, протеворечит моему ЗЫ
Цитата
P.S. оборачивать запрос в другой запрос и сортировать уже результат запроса-врепера не подходит, т.к. помимо сортировки нужны еще LIMIT и OFFSET, и , если оборачивать другим SELECT * from ( SELECT .... ) то теряется весь смысл идеи, т.к. сильно просаживается производительность. А-ля прочитать сначала вообще всё, отсортировать, потом обрезать.


В таблице миллионы записей, нужно работать с LIMIT и OFFSET по уже отсортированному столбцу
Если прочитать подзапросом всю таблицу, потом отсортировать и обрезать LIMIT-ом, запрос будет оооочень долгим.


Цитата (S.Chushkin @ 28.03.2017 - 06:20)
Сначала скажи, какое решение нашли

Изменили условия задачи biggrin.gif


--------------------
[продано копирайтерам]
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




AllesKlar, подожди smile.gif Задача интересная, так что давай "добьём" до конца.
Во-первых, давай все-таки насчет условий задачи определимся smile.gif В то числе с тем, насколько часто меняются данные в исходной таблице.
Во-вторых, есть ли ограничения на объекты (их список) или вообще среди всех искать надо?

Я буду в офисе когда, там чуть перепишу запрос. Тут еще много есть возможностей переделать его, использования специфику Постгре, которая сильно отличается от Мускуля.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




Цитата (AllesKlar @ 29.03.2017 - 00:33)
Цитата (S.Chushkin @ 28.03.2017 - 06:20)
Сначала скажи, какое решение нашли

Изменили условия задачи biggrin.gif

Это не ответ.
Хочешь помощи или дискуссии, будь добр - заинтересуй.


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




Цитата (sergeiss @ 29.03.2017 - 07:48)
Задача интересная

Да ничего интересного.
Встроенных эффективных решений выборки с приоритетом нет, - в mySQL однозначно, в PG тоже нет (насколько знаю).
И все решения давным давно известны - что-то лучше, что-то хуже.


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




Кстати, ТС, твои картинки с alt='user posted image' невидны (не показываются).
upd
Хотя ... может это для старой оперы не показывают назло. В ФФ вижу.


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




Цитата
Но, невозможно отсортировать по полю title т.к. имеем агрегатную функцию, и если сортировать, то сначала нужно сортировать по агрегируемому полю


select DISTINCT on (o.object_id) o.object_id,

Я уже много лет PG не использую и подзабыл малость. sad.gif
Напомните мне, DISTINCT в PG это именно агрегация или он просто удаляет идентичные строки? (в mySQL он просто удаляет дубликаты)


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




Цитата (AllesKlar @ 29.03.2017 - 00:33)
В таблице миллионы записей, нужно работать с LIMIT и OFFSET по уже отсортированному столбцу
Если прочитать подзапросом всю таблицу, потом отсортировать и обрезать LIMIT-ом, запрос будет оооочень долгим.

Ну вот смотри. Если ты делаешь свои джойны, то один хрен ты будешь ворочать эти миллионы записей.
Подзапросы тебе не нравятся, я так понимаю, потому что внутри них нету индексов, поэтому тормозит всё. ОК, меняем запрос...

В одной транзакции создаем временную таблицу из нужных нам данных, делаем к ней индексы, а затем уже крутим-вертим эту таблицу так, как нам надо. В частности, повторяем трюк с временной таблицей и индексами к ней.
Если честно, с большими объемами не проверял.

Вот запрос, рабочий на 100%

drop table if exists tmp_obj;

create temporary table tmp_obj as (
select objects.*, 1 as lang_id from objects where title_lang = 'ru'
union
select
objects.*, 2 as lang_id from objects where title_lang = 'de'
union
select
objects.*, 3 as lang_id from objects where title_lang <> 'ru' and title_lang <> 'de');

create index tmp_obj_object on tmp_obj (object_id, lang_id);

drop table if exists tmp_obj2;

create temporary table tmp_obj2 as (
select distinct on (object_id) object_id, title, title_lang
from tmp_obj
order by object_id, lang_id asc);

create index tmp_obj2_title on tmp_obj2 (title);

select *
from tmp_obj2
order by title;


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




Цитата (S.Chushkin @ 29.03.2017 - 11:01)
Напомните мне, DISTINCT в PG это именно агрегация или он просто удаляет идентичные строки? (в mySQL он просто удаляет дубликаты)

Фактически, тоже удаляет дубликаты. Но что считать дубликатами (какие поля использовать) ты определяешь сам.


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 817
Пользователь №: 36058
На форуме: 4 года, 5 месяцев, 27 дней
Карма: 42




Цитата (sergeiss @ 29.03.2017 - 11:23)
Фактически, тоже удаляет дубликаты.

Т.е. ТС неправ, утверждая, что "Но, невозможно отсортировать по полю title т.к. имеем агрегатную функцию" ?


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15381
Пользователь №: 4190
На форуме: 9 лет, 4 месяца, 18 дней
Карма: 470




Не могу утверждать ничего насчет ТС, т.к. не знаю точно, что он имел ввиду. Придет - сам ответит smile.gif


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
  Быстрый ответ
Информация о Госте
Введите Ваше имя
Кнопки кодов
Для вставки цитаты, выделите нужный текст и
НАЖМИТЕ СЮДА
Введите сообщение
Смайлики
:huh:  :o  ;) 
:P  :D  :lol: 
B)  :rolleyes:  <_< 
:)  :angry:  :( 
:unsure:  :blink:  :ph34r: 
     
Показать всё

Опции сообщения  Включить смайлики?
 Включить подпись?
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темыСтраницы: (2) [1] 2  Ответ в темуСоздание новой темыСоздание опроса