我如何从原始表中创建一个视图，组和平均值？

发布于 2025-01-31 19:18:07 字数 1161 浏览 2 评论 0原文

假设我有一个带有3列的桌子（工作日，Price1和Price2），例如：

create table original_table 
(weekday VARCHAR(15), 
price1 number (6), 
price2 number(6));

insert into original_table values (‘tuesday’, 12, 23);
insert into original_table values (‘monday’, 23, 45);
insert into original_table values (‘friday’, 45, 21);
insert into original_table values (‘friday’, 231, 34);
insert into original_table values (‘monday’, 35, 53);
insert into original_table values (‘tuesday’, 213, 9);

，我想从这个原始表格分组和平均价格创建视图（Price1和Price2）通过特定的专栏（工作日）。留下这样的东西：

我用来创建视图和平均价格的代码。远离我的预期输出，但要开始。

create view view_from_original_table as
weekday, avg_price1, avg_price2
select weekday, avg(price1), avg(price2)
from original_table
group by weekday;

我认为，使用Pivot，我可以实现此结果，但是我很难理解它。

原文

Let’s say I have a table with 3 columns (weekday, price1 and price2), like this:

create table original_table 
(weekday VARCHAR(15), 
price1 number (6), 
price2 number(6));

insert into original_table values (‘tuesday’, 12, 23);
insert into original_table values (‘monday’, 23, 45);
insert into original_table values (‘friday’, 45, 21);
insert into original_table values (‘friday’, 231, 34);
insert into original_table values (‘monday’, 35, 53);
insert into original_table values (‘tuesday’, 213, 9);

And I want to create a view from this original table grouping and averaging the prices (Price1 and Price2) by a specific column (Weekday). Leaving something like this:

Code I am using to create the view and averaging the prices. Faraway from my expected output, but it's to kick off.

create view view_from_original_table as
weekday, avg_price1, avg_price2
select weekday, avg(price1), avg(price2)
from original_table
group by weekday;

I think that with pivot I could achieve this result, but I’m having trouble understanding it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

維他命╮ 2025-02-07 19:18:07

使用案例语句来完成此操作

您可以在此处。 /？rdbms = oracle_11.2＆amp; fiddle=e9f1bec3e9da3319a319a31a92eb9aaaa11b11b11

with t as (select weekday, avg(price1) p1 , avg(price2) p2
from original_table
group by weekday)
select  t.weekday, 
case t.weekday when 'monday' then p1 end as Monday_avg1,
case t.weekday when 'monday' then p2 end as Monday_avg2,
case t.weekday when 'tuesday' then p1 end as Tuesday_avg1,
case t.weekday when 'tuesday' then p2 end as Tuesday_avg2,
case t.weekday when 'friday' then p1 end as Friday_avg1,
case t.weekday when 'friday' then p2 end as Friday_avg2
from t
order by case weekday when 'monday' then 1 when 'tuesday' then 2 else 3 end

you could do it with a case statement

here is the fiddle https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e9f1bec3e9da3319a31a92eb9aa11b11

with t as (select weekday, avg(price1) p1 , avg(price2) p2
from original_table
group by weekday)
select  t.weekday, 
case t.weekday when 'monday' then p1 end as Monday_avg1,
case t.weekday when 'monday' then p2 end as Monday_avg2,
case t.weekday when 'tuesday' then p1 end as Tuesday_avg1,
case t.weekday when 'tuesday' then p2 end as Tuesday_avg2,
case t.weekday when 'friday' then p1 end as Friday_avg1,
case t.weekday when 'friday' then p2 end as Friday_avg2
from t
order by case weekday when 'monday' then 1 when 'tuesday' then 2 else 3 end

回复收藏 0 原文

~没有更多了~