Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7. / 21 сообщений из 21, страница 1 из 1
30.11.2016, 17:39
    #39357857
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Добрый день, коллеги!
Я всего лишь новичок в 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, 17:42
    #39357860
Glays
Glays
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle, я не понял.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 17:43
    #39357864
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_CohleСтолкнулся с задачкой, условие которой в принципе полностью описано в заголовке.Это не условие, это всего лишь попытка его интерпретации в sql-код. Мы не телепаты. Помимо собственно постановки задачи не поленись привести пример исходных данных через with и пример результата
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:04
    #39357890
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Прошу прощения. Пишу полностью всю задачу, с исходными данными.

 
: Задача
Исходные данные:
Представление с продажами 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, 18:15
    #39357903
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Пока что мой запрос имеет такой вид. Но он недоработан(
 
:запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select distinct WEEK_DAY_NUM, SHOP, TYPE, sum(sale_sum) over() as SUM_WEEK
,case 
	when d.WEEK_DAY_NUM between 1 and 4 then avg(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(sale_sum) over (partition by WEEK_DAY_NUM ,SHOP, TYPE order by week_day_num) 
end as AVERAGE 
from V_SALE s
left join V_DAY d ON d.day_id=s.day_id
where WEEK_ID in ('201532','201533')


...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:19
    #39357907
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle,

Если я правильно понимаю условие задачи, то аналитика тут не нужна. Хватит и группировки:
Код: plsql
1.
2.
3.
avg(case WEEK_DAY_NUM between 1 and 4 then sale_sum end),
avg(case WEEK_DAY_NUM between 5 and 7 then sale_sum end)
group by WEEK_ID
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:27
    #39357920
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle,
не то?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
  1  with t (D, sale_sum) as (
  2    select sysdate,10 from dual union all
  3    select sysdate,30 from dual union all
  4    select sysdate+1,10 from dual union all
  5    select sysdate+2,20 from dual union all
  6    select sysdate+3,30 from dual union all
  7    select sysdate+4,40 from dual union all
  8    select sysdate+5,50 from dual union all
  9    select sysdate+6,60 from dual union all
 10    select sysdate+7,70 from dual union all
 11    select sysdate+8,10 from dual )
 12  , sale as (select d,to_char(d,'d') w, sale_sum from t)
 13  select
 14   avg(case when W between 1 and 4 then sum(sale_sum) end ) avg_14
 15  ,avg(case when W between 5 and 5 then sum(sale_sum) end ) avg_57
 16* from sale group by d,w
SQL> /

    AVG_14     AVG_57
---------- ----------
        40         20


.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:33
    #39357933
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
stax..,
В условии задачи "... Сделать за одно обращение к таблице V_SALE в одном запросе, без использования
Common table expression (конструкция with) "
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:38
    #39357944
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohlestax..,
В условии задачи "... Сделать за одно обращение к таблице V_SALE в одном запросе, без использования
Common table expression (конструкция with) "Ответивший тебе и сделал одно обращение к V_SALE, в with вынес ее для декларации, только потому что автор темы поленился/оказался не способным самостоятельно привести пример исходных данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 18:41
    #39357949
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle Сделать за одно обращение к таблице V_SALE в одном запросе, без использования
Common table expression (конструкция with) че за бред??
опять какой-то олдфаг-style teacher отрывается?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:02
    #39357969
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
AmKadRust_Cohle,

Если я правильно понимаю условие задачи, то аналитика тут не нужна. Хватит и группировки:
Код: plsql
1.
2.
3.
avg(case WEEK_DAY_NUM between 1 and 4 then sale_sum end),
avg(case WEEK_DAY_NUM between 5 and 7 then sale_sum end)
group by WEEK_ID


Я же говорю - я новичок, такими приёмами пока не владею!)Я же вроде дал примерный набор исходных данных в описании задачи.
Ваше предложение через обычную группировку по сути делает то же самое, что и мой запрос. Только теперь как вывести общую сумму за две недели? По условию задачи, необходимо же произвести группировку по трем конкретным полям. Разве обычный Group by способен на такое? Заранее прошу прощение за нубские вопросы)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:07
    #39357972
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_CohleТолько теперь как вывести общую сумму за две недели?Разве это есть в постановке задачи?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:22
    #39357982
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
AmKad,
Необходимо по каждому магазину проанализировать продажи за 32, 33 неделю 2015 года
и получить на выходе следующие поля
Список полей:
Номер недели - Группируемое
Магазин - Группируемое
Формат - Группируемое
Суммарные продажи за анализируемый период
Суммарные продажи за день на магазине усредненные до продаж с понедельника по четверг
Суммарные продажи за день на магазине усредненные до продаж с пятницы по воскресенье.

Вот.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:24
    #39357984
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
AmKadХватит и группировки:
Код: plsql
1.
2.
3.
avg(case WEEK_DAY_NUM between 1 and 4 then sale_sum end),
avg(case WEEK_DAY_NUM between 5 and 7 then sale_sum end)
group by WEEK_ID

Тогда уж лучше так
Код: plsql
1.
2.
3.
4.
select
(select avg(sale_sum) from ... where week_day_num between 1 and 4),
(select avg(sale_sum) from ... where week_day_num between 5 and 7)
from dual

может week_day_num проиндексировано.. )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:27
    #39357989
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
--Eugene--AmKadХватит и группировки:
Код: plsql
1.
2.
3.
avg(case WEEK_DAY_NUM between 1 and 4 then sale_sum end),
avg(case WEEK_DAY_NUM between 5 and 7 then sale_sum end)
group by WEEK_ID

Тогда уж лучше так
Код: plsql
1.
2.
3.
4.
select
(select avg(sale_sum) from ... where week_day_num between 1 and 4),
(select avg(sale_sum) from ... where week_day_num between 5 and 7)
from dual

может week_day_num проиндексировано.. )Я привел только часть запроса, который не аналогичен твоему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:37
    #39357998
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_CohleСуммарные продажи за анализируемый периодЯ не случайно прошу (или требую) таких как ты привести пример исходных данных и желаемого результата, не только потому что мне в лом самому набивать тестовые данные, но и потому, что без этого далеко не каждому удается дать однозначную постановку. Вот например, выделенное мною предложение можно трактовать как:
сумму за неделю целиком
или
сумму за две недели целиком.

Если первое (что вероятнее) - то все в рамках группировки. Если второе, то аналитика поверх группировки. Если первое + второе, то rollup.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:48
    #39358001
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
AmKadRust_CohleСуммарные продажи за анализируемый периодЯ не случайно прошу (или требую) таких как ты привести пример исходных данных и желаемого результата, не только потому что мне в лом самому набивать тестовые данные, но и потому, что без этого далеко не каждому удается дать однозначную постановку. Вот например, выделенное мною предложение можно трактовать как:
сумму за неделю целиком
или
сумму за две недели целиком.

Если первое (что вероятнее) - то все в рамках группировки. Если второе, то аналитика поверх группировки. Если первое + второе, то rollup.
Проблема в том, что у меня нет этих данных, у меня есть только задание с листочка бумаги, которое я привел дословно, мне тоже не все однозначно понятно из условия. И предполагается что умный специалист, у которого есть опыт, без какой-либо базы возьмет в голове все это представит и выдаст решение. Лично я у себя попытался примерно задать в базе набор некоторых данных, то как я себе представляю это должно выглядеть. И сижу мучаю эту задачу. Каким образом тебе скинуть мои таблицы, я не представляю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 19:54
    #39358004
Rust_Cohle
Rust_Cohle
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
AmKad выделенное мною предложение можно трактовать как:
сумму за неделю целиком
или
сумму за две недели целиком.

Если первое (что вероятнее) - то все в рамках группировки. Если второе, то аналитика поверх группировки. Если первое + второе, то rollup.
Очевидно раз требуется анализ данных за 32,33 неделю, то и сумма требуется за обе недели.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 20:02
    #39358012
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle,

Отнюдь не очевидно. С учетом того, что явно перечислены группируемые поля.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 20:53
    #39358049
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
Rust_Cohle,

 
от TC хрен дождешься
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with v_sale as (
select date'2015-01-01'+numtodsinterval(30*7, 'day')+mod(level-1,28) DAY_ID, trunc((level-1)/28/3)+1 SHOP, mod(level-1,5)+1 ART_ID, (trunc((level-1)/28/3)+1)*100000 + mod(level-1,3)+1 TYPE, rownum/100 SALE_SUM from dual
connect by level <= 28*3*3*3
),
v_day as (
select date'2015-01-01'+numtodsinterval(30*7, 'day')+mod(level-1,28) DAY_ID, to_number(to_char(date'2015-01-01'+numtodsinterval(31*7, 'day')+mod(level-1,28), 'D')) WEEK_DAY_NUM, to_number(to_char(date'2015-01-01'+numtodsinterval(31*7, 'day')+mod(level-1,28), 'IW')) week_id from dual
connect by level <= 28*3*3*3
)
select d.week_id,
        s.shop,
        s.art_id,
        sum(s.sale_sum) sale_sum,
        sum(case when d.week_day_num between 1 and 4 then s.sale_sum end) sale_sum_1_4,
        sum(case when d.week_day_num between 5 and 7 then s.sale_sum end) sale_sum_5_7
    from v_day d
        join v_sale s
            on s.day_id = d.day_id
        where d.week_id in (32, 33)
    group by rollup (d.week_id,
        s.shop,
        s.art_id)
    order by 1, 2, 3

WEEK_IDSHOPART_IDSALE_SUMSALE_SUM_1_4SALE_SUM_5_732118.648.64321224.578.6415.93321316.4716.47321424.841.0823.76321524.316.28.132198.8225.9272.9322192.6131.3261.29322261.8361.83322392.8823.7669.12322469.6638.8830.7832255454322370.9893.96277.023231107.19107.193232160.9246.44114.483233115.0261.5653.46323499.3699.363235160.6554106.65323643.14162481.143241228.9669.12159.843242160.3884.2476.143243144.72144.723244228.6976.68152.013245152.55152.55324915.3230.04685.263251205.74106.9298.823252190.08190.083253296.7399.36197.373254197.91197.91325529791.8205.23251187.46298.08889.383261235.44235.443262364.77122.04242.733263243.27243.273264365.04114.48250.563265251.1129.6121.53261459.62366.121093.53271432.81144.72288.093272288.63288.633273433.08137.16295.923274296.46152.28144.183275280.8280.83271731.78434.161297.623281333.99333.993282501.12159.84341.283283341.82174.96166.863284326.16326.163285500.85167.4333.453282003.94502.21501.743291569.16182.52386.643292387.18197.64189.543293371.52371.523294568.89190.08378.813295379.35379.353292276.1570.241705.863210687.142682.728004.42331148.621.0627.54331246.7136.729.99331338.3425.9212.42331439.4219.7119.71331531.0510.820.25331204.12114.2189.913321137.43104.7632.673322129.0671.2857.783323130.1465.0765.073324121.7756.1665.61332516289.172.9332680.4386.37294.033331219.78116.64103.143332220.86110.43110.433333212.49101.52110.973334275.4157.14118.263335228.15172.855.353331156.68658.53498.153341311.58155.79155.793342303.21146.88156.333343388.8225.18163.623344318.87240.8478.033345310.5162148.53341632.96930.69702.273351393.93192.24201.693352502.2293.22208.983353409.59308.88100.713354401.22207.36193.863355402.3201.15201.153352109.241202.85906.393361615.6361.26254.343362500.31376.92123.393363491.94252.72239.223364493.02246.51246.513365484.65237.6247.053362585.521475.011110.513371591.03444.96146.073372582.66298.08284.583373583.74291.87291.873374575.37282.96292.413375729429.3299.73373061.81747.171314.633381673.38343.44329.943382674.46337.23337.233383666.09328.32337.773384842.4497.34345.063385681.75513168.753383538.082019.331518.753391765.18382.59382.593392756.81373.68383.133393955.8565.38390.423394772.47581.04191.433395764.1388.8375.33394014.362291.491722.873318983.1610825.658157.5129670.313508.3716161.93
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.11.2016, 21:18
    #39358059
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7.
 
ошибочка
Код: plsql
1.
2.
3.
4.
v_day as (
select date'2015-01-01'+numtodsinterval(30*7, 'day')+mod(level-1,28) DAY_ID, to_number(to_char(date'2015-01-01'+numtodsinterval(30*7, 'day')+mod(level-1,28)-1, 'D')) WEEK_DAY_NUM, to_number(to_char(date'2015-01-01'+numtodsinterval(30*7, 'day')+mod(level-1,28)-1, 'IW')) week_id from dual
connect by level <= 28*3*3*3
)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Найти суммарные продажи за день, усредненные до продаж с 1 по 4 и с 5 по 7. / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 465ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1886915]:
 0ms
get settings:
 7ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 158ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 220ms
total:  465ms
x
x
Закрыть


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