ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
15 сообщений из 15, страница 1 из 1
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39357731
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Добрый день, коллеги!
Я всего лишь новичок в sql. Столкнулся с задачкой, условие которой в принципе полностью описано в заголовке.
Насколько я понимаю, подобного рода задачи решаются с использование аналитических функций.
Я представляю себе решение так:

Код: sql
1.
2.
3.
4.
5.
6.
Select ....
case 
	when d.WEEK_DAY_NUM between 1 and 4 then avg(sum(sale_sum)) over (partition by WEEK_DAY_NUM ,SHOP, TYPE order by week_day_num ) 
	when d.WEEK_DAY_NUM between 5 and 7 then avg(sum(sale_sum)) over (partition by WEEK_DAY_NUM ,SHOP, TYPE order by week_day_num )
end as AVERAGE 
...


P.S. WEEK_DAY_NUM - номер дня недели(от 1 до 7)
У меня предчувствие, что кейс тут и не нужен, просто необходимо корректно задать интервал после "order by week_day_num", а вот как это сделать пока не пойму. И еще можно ли таким способом вычислять среднюю сумму?
Помогите пл3 люди добрые!)
...
Рейтинг: 0 / 0
30.11.2016, 15:25
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39357775
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
1) MySQL не знает про оконные функции.
2) Очень мутная формулировка. Попробуйте сформулировать вопрос более вменяемо. КВ крайнем случае примером.
...
Рейтинг: 0 / 0
30.11.2016, 16:10
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39357792
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Akina, вопрос задачи передал полностью. Признаюсь, мне он тоже не совсем понятен. Но что есть, то есть.
Повторю - необходимо вывести.

Суммарные продажи за день на магазине усредненные до продаж с понедельника по четверг
Суммарные продажи за день на магазине усредненные до продаж с пятницы по воскресенье.
Все данные имеются: поле номера дня недели (WEEK_DAY_NUM), поле суммы продаж за определенный магазин, определенного типа магазина, определенного вида товара, текущей даты(SALE_SUM). Остальная информация избыточна. Я точно уверен что решается путем применения аналитических функций. Примерный вид я написал, но его как то надо доработать, чтобы за окна(1-4 и 5-7) вычислялась на каждый день эта усредненная сумма.
...
Рейтинг: 0 / 0
30.11.2016, 16:35
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358023
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
То есть нужна среднесуточная продажа в период с понедельника по четверг и с пятницы по воскресенье?
Ну не вижу проблемы... будет что-то типа

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT WEEK(`date`) AS `week`
, SUM(CASE WHEN DAYOFWEEK(`date`) BETWEEN 2 AND 5  THEN `sale_sum` END) / 4 AS `avg1`
, SUM(CASE WHEN NOT (DAYOFWEEK(`date`) BETWEEN 2 AND 5)  THEN `sale_sum` END) / 3 AS `avg2`
-- , остальные поля
FROM `datatable`
GROUP BY WEEK(`date`)
-- , остальные поля
...
Рейтинг: 0 / 0
30.11.2016, 20:19
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358085
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
AkinaТо есть нужна среднесуточная продажа в период с понедельника по четверг и с пятницы по воскресенье?
Ну не вижу проблемы... будет что-то типа

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT WEEK(`date`) AS `week`
, SUM(CASE WHEN DAYOFWEEK(`date`) BETWEEN 2 AND 5  THEN `sale_sum` END) / 4 AS `avg1`
, SUM(CASE WHEN NOT (DAYOFWEEK(`date`) BETWEEN 2 AND 5)  THEN `sale_sum` END) / 3 AS `avg2`
-- , остальные поля
FROM `datatable`
GROUP BY WEEK(`date`)
-- , остальные поля


Вы имели в виду ?
Код: sql
1.
2.
, SUM(CASE WHEN DAYOFWEEK(`date`) BETWEEN 1 AND 4  THEN `sale_sum` END) / 4 AS `avg1`
, SUM(CASE WHEN DAYOFWEEK(`date`) BETWEEN 5 AND 7  THEN `sale_sum` END) / 3 AS `avg2`


Вот целиком условие задачи. Я чет с самого начала подумал про аналитические функции. Возможно все проще. Признаюсь условие задачи замудренное и весьма непонятно сформулировано.
 
:Задача
Исходные данные:
Представление с продажами V_SALE следующей структуры
DAY_ID -- День продажи, тип дата
SHOP -- Магазин
ART_ID -- Товар
TYPE-- Тип (1, 2, 3) --зависит от магазина
SALE_SUM - Сумма продажи
Первые три поля являются уникальным ключом
Строк с нулевыми продажами в таблице нет.

Представление дней недели V_DAY
DAY_ID - День, тип дата
WEEK_DAY_NUM - Номер для недели в числовом формате Понедельник = 1, воскресенье = 7
WEEK_ID - Номер года и недели в года в формате 201532

Задача:
Необходимо по каждому магазину проанализировать продажи за 32, 33 неделю 2015 года
и получить на выходе следующие поля
День, когда в магазине отсутствуют продажи можно не учитывать при рассчете средней.
Сделать за одно обращение к таблице V_SALE в одном запросе, без использования
Common table expression (конструкция with)

Список полей:
Номер недели - Группируемое
Магазин - Группируемое
Формат - Группируемое
Суммарные продажи за анализируемый период
Суммарные продажи за день на магазине усредненные до продаж с понедельника по четверг
Суммарные продажи за день на магазине усредненные до продаж с пятницы по воскресенье.
...
Рейтинг: 0 / 0
30.11.2016, 22:39
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358086
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Rust_CohleВы имели в видуПопробуйте почитать справку по функции, что ли...
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofweek
...
Рейтинг: 0 / 0
30.11.2016, 22:41
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358087
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Как вы относитесь к такому варианту решения?
Код: sql
1.
2.
3.
4.
5.
6.
7.
select WEEK_ID, SHOP, TYPE, sum(sale_sum) as summa,
        avg(case when d.week_day_num between 1 and 4 then s.sale_sum end) as 1_4,
        avg(case when d.week_day_num between 5 and 7 then s.sale_sum end) as 5_7
from V_SALE s
left join V_DAY d ON d.day_id=s.day_id
where WEEK_ID in ('201532','201533')
group by WEEK_ID, SHOP, TYPE
...
Рейтинг: 0 / 0
30.11.2016, 22:57
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358091
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Rust_CohleВот целиком условие задачи
[skipped]
День, когда в магазине отсутствуют продажи можно не учитывать при рассчете средней.
Вот будь там слово "нужно" - я бы понял. А так получается - считай, как заблагорассудится. И, кстати, 19953120 построен для того, чтобы УЧИТЫВАТЬ такие дни. Если их не учитывать, то делить надо не на 3 или 4, а на COUNT(DISTINCT `date`).

Rust_CohleПредставление дней недели V_DAY
DAY_ID - День, тип дата
WEEK_DAY_NUM - Номер для недели в числовом формате Понедельник = 1, воскресенье = 7
WEEK_ID - Номер года и недели в года в формате 201532
Вот оно чё, Михалыч... а где гарантия, что данные согласованы? что и день недели, и номер недели правильно соответствуют дате....
...
Рейтинг: 0 / 0
30.11.2016, 23:06
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358093
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Akina,
А нельзя обойтись просто функцией AVG()? Ей же не важно, есть дни без продаж или же их нет. Она посчитает среднее по тому количеству, которое имеется по факту.
...
Рейтинг: 0 / 0
30.11.2016, 23:22
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358161
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Rust_CohleА нельзя обойтись просто функцией AVG()?Ему нужна не средняя сумма одной продажи, а средняя суммарная продажа за день, т.е. AVG(SUM()). А это либо подзапрос и 2 группировки, либо эмуляция AVG на кейсах и одна группировка. Мне кажется, что второй вариант будет быстрее - впрочем, зависит от структуры и наполнения, нужно тестить.
...
Рейтинг: 0 / 0
01.12.2016, 08:03
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358358
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Akina,
Благодарю вас за помощь!
У этой задачки есть еще одно дополнительное условие.
"Вывести такой же набор полей, только все магазины с TYPE=2 и =3 сгруппировать до типа, а для магазинов с TYPE=1 оставить детализацию до магазина." Всего 3 типа магазинов.
Пока не могу сообразить как это все перегруппировать:(
...
Рейтинг: 0 / 0
01.12.2016, 12:26
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358365
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Получается. что у наборов разные уровни группировки. делай 2 отдельных запроса, и Union all.
...
Рейтинг: 0 / 0
01.12.2016, 12:28
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358532
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
AkinaПолучается. что у наборов разные уровни группировки. делай 2 отдельных запроса, и Union all.
Получается, что второй селект будет такой же за исключением: уровень группировки не по 3 полям, а по 2. Но тогда и количество выводимых данных изменится на -1, а оно должно быть одинаковым при использовании Union all. Подскажите как эту коллизию обойти?
Мой запрос возвращает ошибку - "Все запросы, объединенные с помощью операторов UNION, INTERSECT или EXCEPT, должны иметь одинаковое число выражений в целевых списках."
 
:Запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select  WEEK_ID, SHOP, TYPE, sum(sale_sum) sale_sum,
        sum(case when d.week_day_num between 1 and 4 then s.sale_sum end)/4 as avg_1_4,
        sum(case when d.week_day_num between 5 and 7 then s.sale_sum end)/3 as avg_5_7
from V_SALE s
left join V_DAY d ON d.day_id=s.day_id
where WEEK_ID in ('201532','201533') and TYPE=1
group by WEEK_ID, SHOP, TYPE
union all 
select  WEEK_ID, TYPE, sum(sale_sum) sale_sum,
        sum(case when d.week_day_num between 1 and 4 then s.sale_sum end)/4 as avg_1_4,
        sum(case when d.week_day_num between 5 and 7 then s.sale_sum end)/3 as avg_5_7
from V_SALE s
left join V_DAY d ON d.day_id=s.day_id
where WEEK_ID in ('201532','201533') and TYPE in (2,3)
group by WEEK_ID, TYPE


...
Рейтинг: 0 / 0
01.12.2016, 15:01
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358638
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Rust_Cohleтогда и количество выводимых данных изменится на -1
Ну добавь недостающее поле литералом:

Код: sql
1.
2.
3.
SELECT week, shop, type FROM ... GROUP BY week, shop, type
UNION ALL
SELECT week, NULL, type FROM ... GROUP BY week, type
...
Рейтинг: 0 / 0
01.12.2016, 16:36
| Ответить | Цитировать | Написать  
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
    #39358803
Rust_Cohle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rust_Cohle
Гость
Akina,

Спасибо большое)
И самое последнее доп. условие к текущей задачи следующее.
Необходимо вывести тот же список полей, только вывести группировку как в разрезе магазинов,
так и в разрезе только до уровня типов, без детализации до магазинов.
Группировка до недели остается.
Тут я вообще теряюсь в догадках.
...
Рейтинг: 0 / 0
01.12.2016, 18:16
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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