如何简化 Oracle 中的 SQL 查询?
经过一番搜索和阅读后,我为我的应用程序提出了以下 SQL 查询:
SELECT
ROUND(AVG(CASE WHEN gender = 'M' THEN rating END), 1) avgAllM,
COUNT(CASE WHEN gender = 'M' THEN rating END) countAllM,
ROUND(AVG(CASE WHEN gender = 'F' THEN rating END), 1) avgAllF,
COUNT(CASE WHEN gender = 'F' THEN rating END) countAllF,
ROUND(AVG(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18M,
COUNT(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END) countU18M,
ROUND(AVG(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18F,
COUNT(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END) countU18F
FROM movie_ratings mr INNER JOIN accounts a
ON mr.aid = a.aid
WHERE mid = 5;
如果可能的话,我想知道如何简化它。 birth_date
字段的类型为 DATE
,而 UserAge
是根据该日期字段计算年龄的函数。
表结构如下:
[ACCOUNTS]
aid(PK), birth_date, gender
[MOVIE_RATINGS]
mid(PK), aid(PK,FK), rating
我正在寻找两件事:
- 对上面代码的一般简化,经验丰富的用户知道但我不知道。
- 我在 PHP 中执行此操作,对于每条记录,我都会有一个包含所有这些变量的关联数组。我正在寻找一种方法将它们分组为多维数组,以便 PHP 代码更易于阅读。当然我不想在 PHP 本身中这样做,那是没有意义的。
例如,这样的事情:
$info[0]['avgAllM']
$info[0]['countAllM']
$info[1]['avgAllF']
$info[1]['countAllF']
$info[2]['avgU18M']
$info[2]['countU18M']
$info[3]['avgU18F']
$info[3]['countU18F']
而不是:
$info['avgAllM']
$info['countAllM']
$info['avgAllF']
$info['countAllF']
$info['avgU18M']
$info['countU18M']
$info['avgU18F']
$info['countU18F']
我什至不知道这是否可能,所以我真的想知道它是否可能以及如何做到。
为什么我想要这一切?好吧,上面的 SQL 查询只是我需要执行的完整 SQL 的一个片段。我还没有这样做,因为在完成所有工作之前,我想知道是否有更紧凑的 SQL 查询来实现相同的结果。基本上,我会添加一些与上面类似的行,但条件不同,特别是在日期上。
After searching and reading a little bit I came up with the following SQL query for my application:
SELECT
ROUND(AVG(CASE WHEN gender = 'M' THEN rating END), 1) avgAllM,
COUNT(CASE WHEN gender = 'M' THEN rating END) countAllM,
ROUND(AVG(CASE WHEN gender = 'F' THEN rating END), 1) avgAllF,
COUNT(CASE WHEN gender = 'F' THEN rating END) countAllF,
ROUND(AVG(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18M,
COUNT(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END) countU18M,
ROUND(AVG(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18F,
COUNT(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END) countU18F
FROM movie_ratings mr INNER JOIN accounts a
ON mr.aid = a.aid
WHERE mid = 5;
And I'm wondering how can I simplify this, if possible. The birth_date
field is of type DATE
and UserAge
is a function to calculate the age from that date field.
The table structures are as follows:
[ACCOUNTS]
aid(PK), birth_date, gender
[MOVIE_RATINGS]
mid(PK), aid(PK,FK), rating
I'm looking for two things:
- General simplifications to the code above that more experienced users know about that I don't.
- I'm doing this in PHP and for each record I'll have an associative array with all those variables. I'm looking for a way to group them into a multidimensional array, so the PHP code is easier to read. Of course I don't want to do this in PHP itself, it would be pointless.
For instance, something like this:
$info[0]['avgAllM']
$info[0]['countAllM']
$info[1]['avgAllF']
$info[1]['countAllF']
$info[2]['avgU18M']
$info[2]['countU18M']
$info[3]['avgU18F']
$info[3]['countU18F']
Instead of:
$info['avgAllM']
$info['countAllM']
$info['avgAllF']
$info['countAllF']
$info['avgU18M']
$info['countU18M']
$info['avgU18F']
$info['countU18F']
I don't even know if this is possible, so I'm really wondering if it is and how it can be done.
Why I want all this? Well, the SQL query above is just a fragment of the complete SQL I need to do. I haven't done it yet because before doing all the work, I want to know if there's a more compact SQL query to achieve the same result. Basically I'll add a few more lines like the ones above but with different conditions, specially on the date.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以使用以下定义创建一个
VIEW
然后从中选择
可能会稍微简化一些事情?
You could create a
VIEW
with the following definitionThen SELECT from that
Might simplify things slightly?
这可能只是优化得太早的情况。该查询可以满足您的需要,而且实际上看起来很复杂,因为事实确实如此。我不确定是否有任何技巧会有帮助。这可能取决于您的数据的特征。查询速度慢吗?你认为可以更快吗?
可能值得按以下方式重新排列它。由于所有条件都依赖于 ACCOUNTS 表,我认为该表将明显小于 MOVIE_RATINGS 表,因此您可能能够在较小的数据集上进行所有计算,这可能会更快。不过,如果您一次只选择一部电影 (
mid = 5
),那么情况可能并非如此。我不完全确定这会起作用,但认为它应该起作用。
但总的来说,我可能会保留您的查询原样。您的查询很容易理解并且可能执行得相当好。
This could just be a case of optimizing too early. The query does what you need and only really looks complicated because it is. I'm not sure that there are necessarily any tricks that would help. It probably depends on the characteristics of your data. Is the query slow? Do you think it could be quicker?
It might be worth rearranging it in the following way. Since all the conditions rely on the
ACCOUNTS
table which I assume is going to be significantly smaller than theMOVIE_RATINGS
table you might be able to do all the calculations on a smaller data set, which might be quicker. Although if you are only selecting on one movie at a time (mid = 5
) then that probably won't be the case.I'm not entirely sure that this will work but think it should.
On balance though, I would probably just leave the query you have as it is. The query that you have is easy to understand and probably performs fairly well.