SQL 请教 - Database版

本页内容为未名空间相应帖子的节选和存档，一周内的贴子最多显示50字，超过一周显示500字访问原贴

相关主题
● 有人给大包子了	● Merge table with one single query?
● 这道SQL题目怎么解	● SQL Conditional Select
● MS T-SQL 问题	● 问一个SQL Server的问题
● 这2个query哪个快点，为啥	● 请教大牛一道有趣的SQL题
● How to get other columns after UNION?	● error of executing SQL query of string concatenation (转载
● sql的2个问题 (转载)	● How to query a tree
● [转载] Can anyone interpret this simple SQL?	● SQL问题请教
● 请教2个sql query 问题	● SQL Server query 一问

相关话题的讨论汇总
话题: rating话题: select话题: 01话题: insert话题: values

进入Database版参与讨论

(共1页)

w********m
发帖数: 1137

有那位大侠知道这道题怎么做吗？
create table Rating(reviewerID int, movieID int, ratingScore int, ratingDate
date);
insert into Rating values(201, 101, 2, '2011-01-22');
insert into Rating values(201, 101, 4, '2011-01-27');
insert into Rating values(202, 106, 4, null);
insert into Rating values(203, 103, 2, '2011-01-20');
insert into Rating values(203, 108, 4, '2011-01-12');
insert into Rating values(203, 108, 2, '2011-01-30');
insert into Rating values(204, 101, 3, '2011-01-09');
insert into Rating values(205, 103, 3, '2011-01-27');
insert into Rating values(205, 104, 2, '2011-01-22');
insert into Rating values(205, 108, 4, null);
insert into Rating values(206, 107, 3, '2011-01-15');
insert into Rating values(206, 106, 5, '2011-01-19');
insert into Rating values(207, 107, 5, '2011-01-20');
insert into Rating values(208, 104, 3, '2011-01-02');
insert into Rating values(208, 104, 3, '2011-01-02');
-- For all cases where the same reviewer rated the same movie
-- twice and gave it a higher rating the second time.

B*****g
发帖数: 34098

90％+数据库版的SQL问题可以用partition by解决

ratingDate

【在 w********m 的大作中提到】

: 有那位大侠知道这道题怎么做吗？
: create table Rating(reviewerID int, movieID int, ratingScore int, ratingDate
: date);
: insert into Rating values(201, 101, 2, '2011-01-22');
: insert into Rating values(201, 101, 4, '2011-01-27');
: insert into Rating values(202, 106, 4, null);
: insert into Rating values(203, 103, 2, '2011-01-20');
: insert into Rating values(203, 108, 4, '2011-01-12');
: insert into Rating values(203, 108, 2, '2011-01-30');
: insert into Rating values(204, 101, 3, '2011-01-09');

T****U
发帖数: 3344

let me try:
select * from Rating a Join
(select reviewId, movieId, max(ratingScore) maxSalary,
max(ratingDate) latestDate
from Rating group by reviewId, movieID
having count(*)=2 and avg(ratingScore) using(reviewId,moveieId)
where ratingScore = maxSalary and ratingDate = latestDate;

ratingDate

【在 w********m 的大作中提到】

j*****n
发帖数: 1781

--Good for MSSQLSRV2K5 and higher versions
;WITH Rating (reviewerID , movieID , ratingScore , ratingDate)
AS
(
SELECT 201, 101, 2, '2011-01-22'
UNION ALL
SELECT 201, 101, 4, '2011-01-27'
UNION ALL
SELECT 202, 106, 4, null
UNION ALL
SELECT 203, 103, 2, '2011-01-20'
UNION ALL
SELECT 203, 108, 4, '2011-01-12'
UNION ALL
SELECT 203, 108, 2, '2011-01-30'
UNION ALL
SELECT 204, 101, 3, '2011-01-09'
UNION ALL
SELECT 205, 103, 3, '2011-01-27'
UNION ALL
SELECT 205, 104, 2, '2011-01-22'
UNION ALL
SELECT 205, 108, 4, null
UNION ALL
SELECT 206, 107, 3, '2011-01-15'
UNION ALL
SELECT 206, 106, 5, '2011-01-19'
UNION ALL
SELECT 207, 107, 5, '2011-01-20'
UNION ALL
SELECT 208, 104, 3, '2011-01-02'
UNION ALL
SELECT 208, 104, 3, '2011-01-02'
),
-- For all cases where the same reviewer rated the same movie twice and gave
it a higher rating the second time.
TwiceRating
AS (
SELECT reviewerID, movieID
FROM Rating
Group by reviewerID, movieID
Having Count(1) = 2
),
RatingList
AS (
SELECT A.*, ROW_NUMBER() OVER (PARTITION BY A.reviewerID, A.movieID ORDER BY
A.ratingDate ASC) AS RN
FROM Rating A JOIN TwiceRating B ON A.reviewerID = B.reviewerID AND A.
movieID = B.movieID
)
SELECT A.reviewerID, A.movieID, B.ratingScore AS the2ndHigherRatingScore
FROM RatingList A JOIN RatingList B
ON A.reviewerID = B.reviewerID AND A.movieID = B.movieID AND A.RN = 1 AND B.
RN = 2
WHERE A.ratingScore < B.ratingScore

【在 B*****g 的大作中提到】

: 90％+数据库版的SQL问题可以用partition by解决
:
: ratingDate

j*****n
发帖数: 1781

syntax error...

maxSalary,
and

【在 T****U 的大作中提到】

: let me try:
: select * from Rating a Join
: (select reviewId, movieId, max(ratingScore) maxSalary,
: max(ratingDate) latestDate
: from Rating group by reviewId, movieID
: having count(*)=2 and avg(ratingScore): using(reviewId,moveieId)
: where ratingScore = maxSalary and ratingDate = latestDate;
:
: ratingDate

T****U
发帖数: 3344

thx, modified. does it work now?

【在 j*****n 的大作中提到】

: syntax error...
:
: maxSalary,
: and

j*****n
发帖数: 1781

yap

【在 T****U 的大作中提到】

: thx, modified. does it work now?

B*****g
发帖数: 34098

how to handle ratingDate NULL?
how to handle equal ratingDate?

ratingDate

【在 w********m 的大作中提到】

w********m
发帖数: 1137

Nice。看来一步还是做的出来的。包子已发。谢谢。

b

【在 T****U 的大作中提到】

T****U
发帖数: 3344

客气,其他两个mm说得也有道理的

【在 w********m 的大作中提到】

: Nice。看来一步还是做的出来的。包子已发。谢谢。
:
: b

相关主题
● sql的2个问题 (转载)	● Merge table with one single query?
● [转载] Can anyone interpret this simple SQL?	● SQL Conditional Select
● 请教2个sql query 问题	● 问一个SQL Server的问题
进入Database版参与讨论

w********m
发帖数: 1137

这个是stanford database公开课上的一道习题，要用ANSI SQL(SQLite)做一个query。
像北京大侠考虑的database design好像还没涉及。

【在 B*****g 的大作中提到】

: how to handle ratingDate NULL?
: how to handle equal ratingDate?
:
: ratingDate

B*****g
发帖数: 34098

他们竟然用SQLite教？还不如直接上小布老师的SQL视屏呢。另外ANSI SQL和SQLite有
什么关系？

【在 w********m 的大作中提到】

: 这个是stanford database公开课上的一道习题，要用ANSI SQL(SQLite)做一个query。
: 像北京大侠考虑的database design好像还没涉及。

T****U
发帖数: 3344

小布老师讲得挺好的，除了英语发音不如stanford

【在 B*****g 的大作中提到】

: 他们竟然用SQLite教？还不如直接上小布老师的SQL视屏呢。另外ANSI SQL和SQLite有
: 什么关系？

T****U
发帖数: 3344

stanford讲得好象很杂, 每个东西都不细,就没有仔细听

【在 w********m 的大作中提到】

: 这个是stanford database公开课上的一道习题，要用ANSI SQL(SQLite)做一个query。
: 像北京大侠考虑的database design好像还没涉及。

B*****g
发帖数: 34098

假设（其实不用假设，必须考虑）score和data都需要考虑NULL和equals，写一个SQL，
呵呵

【在 T****U 的大作中提到】

: 小布老师讲得挺好的，除了英语发音不如stanford

w********m
发帖数: 1137

stanford讲的蛮好，很多题跟很多面试题很像。
SQL Server做这道题偶觉得pivot一下就好了。标准SQL做还真是不容易。
小布老师是什么课程，有链接吗？

【在 B*****g 的大作中提到】

: 他们竟然用SQLite教？还不如直接上小布老师的SQL视屏呢。另外ANSI SQL和SQLite有
: 什么关系？

T****U
发帖数: 3344

boobooke.com

【在 w********m 的大作中提到】

: stanford讲的蛮好，很多题跟很多面试题很像。
: SQL Server做这道题偶觉得pivot一下就好了。标准SQL做还真是不容易。
: 小布老师是什么课程，有链接吗？

B*****g
发帖数: 34098

什么叫“score为null就不返回了”？
date相等的时候多了去了，double click submit button的人有的是

【在 T****U 的大作中提到】

: boobooke.com

T****U
发帖数: 3344

score 为null, 就没法比较，本来就不应该有值返回，这点sql逻辑和本来的工作逻辑是
一样的,不影响输出结果。一定要管，那就加个nvl什么的,不过那样null的会相等，和
原来的null含义也不完全一样。我觉得不加比较好。
date可以加一个min(date)
【在 B*****g 的大作中提到】

: 什么叫“score为null就不返回了”？
: date相等的时候多了去了，double click submit button的人有的是

w********m
发帖数: 1137

同意ThinkU。要比较的数值null的时候，SQL就不理了，神也没有办法，呵呵。

相关主题
● 请教大牛一道有趣的SQL题	● SQL问题请教
● error of executing SQL query of string concatenation (转载	● SQL Server query 一问
● How to query a tree	● 求教个MS SQL的问题
进入Database版参与讨论

w********m
发帖数: 1137

两个日期一样的时候，就只能比较hour, minute, second

B*****g
发帖数: 34098

搞来搞去，实际上用agg扩展性比较差，而ana在这方面是比较灵活的，特殊情况都是小
改就行了，看看俺这个，大家讨论一下效率如何，哈哈，就是要证明能用partition by
解决
WITH tmp AS (
SELECT r.*,
COUNT(*) OVER (PARTITION BY reviewerID, movieID) review_count,
RANK() OVER (PARTITION BY reviewerID, movieID ORDER BY RATINGDate)
review_date_order,
RANK() OVER (PARTITION BY reviewerID, movieID ORDER BY RATINGScore
ASC NULLS FIRST) review_score_order
FROM rating r)
SELECT t.reviewerID, t.movieID
FROM tmp t
WHERE t.review_count = 2
AND t.review_date_order = 2
AND t.review_score_order = 2

辑是

【在 T****U 的大作中提到】

: score 为null, 就没法比较，本来就不应该有值返回，这点sql逻辑和本来的工作逻辑是
: 一样的,不影响输出结果。一定要管，那就加个nvl什么的,不过那样null的会相等，和
: 原来的null含义也不完全一样。我觉得不加比较好。
: date可以加一个min(date)

B*****g
发帖数: 34098

嘿嘿

【在 w********m 的大作中提到】

: 同意ThinkU。要比较的数值null的时候，SQL就不理了，神也没有办法，呵呵。

T****U
发帖数: 3344

MM，你这里date/score为null, 或相等的情况不好办吧
score一个为null, 一个为3，难道输出？这个业务逻辑不知道对不对？

by

【在 B*****g 的大作中提到】

: 搞来搞去，实际上用agg扩展性比较差，而ana在这方面是比较灵活的，特殊情况都是小
: 改就行了，看看俺这个，大家讨论一下效率如何，哈哈，就是要证明能用partition by
: 解决
: WITH tmp AS (
: SELECT r.*,
: COUNT(*) OVER (PARTITION BY reviewerID, movieID) review_count,
: RANK() OVER (PARTITION BY reviewerID, movieID ORDER BY RATINGDate)
: review_date_order,
: RANK() OVER (PARTITION BY reviewerID, movieID ORDER BY RATINGScore
: ASC NULLS FIRST) review_score_order

B*****g
发帖数: 34098

这就是显示一下可调，具体NULL first还是last可以改动

【在 T****U 的大作中提到】

: MM，你这里date/score为null, 或相等的情况不好办吧
: score一个为null, 一个为3，难道输出？这个业务逻辑不知道对不对？
:
: by

(共1页)

进入Database版参与讨论

相关主题
● SQL Server query 一问	● How to get other columns after UNION?
● 求教个MS SQL的问题	● sql的2个问题 (转载)
● Oracle 11g坑爹还是公司DBA坑爹	● [转载] Can anyone interpret this simple SQL?
● 一个SQL写法性能的请教	● 请教2个sql query 问题
● 有人给大包子了	● Merge table with one single query?
● 这道SQL题目怎么解	● SQL Conditional Select
● MS T-SQL 问题	● 问一个SQL Server的问题
● 这2个query哪个快点，为啥	● 请教大牛一道有趣的SQL题

相关话题的讨论汇总
话题: rating话题: select话题: 01话题: insert话题: values

#	版面	帖数(主题数)
-	全站	4871 (796)
1	Military	3777 (569)
2	Stock	341 (51)
3	Joke	117 (17)
4	History	116 (3)
5	Automobile	100 (9)
6	USANews	55 (9)
7	Midlife	45 (1)
8	Headline	41 (41)
9	Dreamer	33 (13)
10	FleaMarket	32 (20)
11	Living	30 (7)

boards

未名新帖统计// 7月16日

历史上的今天