群里一个数据库讨论题

key 发表于 24-8-2009 23:53:26

先给题目，看看大家有什么想法。

有一个数据库，有字段 x , 和 y，如果 y 一共有4个值，而x是从1开始顺次递增的值。现在想把数据库分成每40个值一组，对每一组数据依次统计y为0, 1, 2, 3的结果。。。。
用一条sql来解决，不能使用动态sql，尽量符合SQL标准（如SQL99, SQL92之流）

最好能列出五个列
x的起始值, y=0统计值, y=1统计值, y=2统计值, y=3统计值

关于我那个群，可以查看我前面的发文。hoho~

补充说明一下，小弟开群的目的是讨论技术问题。如果很想知道有关移民或澳洲的IT现状，
我觉得freeoz论坛的移民专题以及工作版能提供更多的帮助。

yuba 发表于 25-8-2009 00:28:34

想起了很多年前那些只有一行的BASIC程序

coredump 发表于 25-8-2009 00:34:06

SELECT
a, count(y = 0), count(y=1), count(y=2), count(y=3)
FROM
(SELECT (x/40 + 1) as a, y FROM TABLENAME)
GROUP BY a;

其中count(y=0)这样是不是标准用法那不准，如果不是还得用别的方法写

ubuntuhk 发表于 25-8-2009 00:43:03

回复 #1 key 的帖子

对不起，看了2遍，没看懂题目，放弃:L :L :L

coredump 发表于 25-8-2009 00:59:46

使用DECODE函数的写法(适合ORACLE,是否标准不知),我认为这个题目主要考察如何根据一个列的值生成新的列

SELECT
a, count(y0), count(y1), count(y2), count(y3)
FROM
(SELECT
   (x/40 + 1) as a,
   DECODE(y,0, 0, NULL) as y0,
   DECODE(y,1, 1, NULL) as y1,
   DECODE(y,2, 2, NULL) as y2,
   DECODE(y,3, 3, NULL) as y3,
FROM TABLENAME)
GROUP BY a;

coredump 发表于 25-8-2009 09:44:58

刚才查了下SQL 92标准，发现DECODE不是标准，对应的符合SQL92的应该使用CASE表达式(6.9case expression in SQL 92). 相比DECODE, CASE表达式稍显罗嗦

SELECT
a, count(y0), count(y1), count(y2), count(y3)
FROM
(SELECT
   (x/40 + 1) as a,
   CASE WHEN y = 0 THEN 0 ELSE NULL END y0,
   CASE WHEN y = 1 THEN 1 ELSE NULL END y1,
   CASE WHEN y = 2 THEN 2 ELSE NULL END y2,
   CASE WHEN y = 3 THEN 3 ELSE NULL END y3,
FROM TABLENAME)
GROUP BY a;

Port_1433 发表于 25-8-2009 10:21:33

本来很有兴趣挑战一下的，不过不好意思，我承认我没有看明白题目。:$ :$ :$

周星星1832 发表于 25-8-2009 10:27:32

没看懂题:lol

yuba 发表于 25-8-2009 10:57:42

题目和答案都没看懂

someonehappy 发表于 25-8-2009 12:27:36

题目里面有个地方没讲清楚吧
数据分40个一组，那么是按照什么规则来分？估计是按照x的顺序来分，比如x 1-40 第一组，41-80第二组，以此类推。

另外，coredump的答案好像有点问题，把x变成了2，3，。。。，39，40，41，2，3，。。。，，39，40，41的序列a，然后根据a的值再进行分组统计。这样不是把数据分成40个一组，一共无数组（原题要求），而是把所有数据分成了40组，每组无数个数据。

[ 本帖最后由 someonehappy 于 25-8-2009 11:36 编辑 ]

coredump 发表于 25-8-2009 13:47:29

原帖由 someonehappy 于 25-8-2009 11:27 发表 http://cn.freeoz.org/forum/images/common/back.gif
题目里面有个地方没讲清楚吧
数据分40个一组，那么是按照什么规则来分？估计是按照x的顺序来分，比如x 1-40 第一组，41-80第二组，以此类推。

另外，coredump的答案好像有点问题，把x变成了2，3，。。。，39，40 ...
我也是这样理解的，你好像把除法理解成了MOD操作，不过我的答案的确有错，我不应该写成 (x/40 + 1)，也应该写成(x-1)/40 + 1 ，因为x是从1开始的。
这样 x = 1则
(x-1)/40 + 1 = （1 -1)/40 +1 = 1
...
x = 40
(x-1)/40 +1 = (40 -1)/40 +1 = 1
这样就把按照x顺序被40个连续的值变成40个里面的最小值了，修正后的答案：
SELECT
a, count(y0), count(y1), count(y2), count(y3)
FROM
(SELECT
   ((x-1)/40 + 1) as a,
   CASE WHEN y = 0 THEN 0 ELSE NULL END y0,
   CASE WHEN y = 1 THEN 1 ELSE NULL END y1,
   CASE WHEN y = 2 THEN 2 ELSE NULL END y2,
   CASE WHEN y = 3 THEN 3 ELSE NULL END y3,
FROM TABLENAME)
GROUP BY a;

earthengine 发表于 25-8-2009 14:25:04

以下是使用子查询的思路，虽然也罗嗦，但是很规整：

SELECT (x-1)/40 + 1 as a,
   sum( (SELECT count(*) FROM TABLENAME WHERE y=0 AND (x-1)/40 = (outtable.x-1)/40) ) as y0,
   sum( (SELECT count(*) FROM TABLENAME WHERE y=1 AND (x-1)/40 = (outtable.x-1)/40 ) as y1,
   sum( (SELECT count(*) FROM TABLENAME WHERE y=2 AND (x-1)/40 = (outtable.x-1)/40) ) as y2,
   sum( (SELECT count(*) FROM TABLENAME WHERE y=3 AND (x-1)/40 = (outtable.x-1)/40 ) as y3
FROM TABLENAME outtable
GROUP BY (x-1)/40

[ 本帖最后由 earthengine 于 25-8-2009 13:27 编辑 ]

coredump 发表于 25-8-2009 14:28:18

回复 #12 earthengine 的帖子

好像按照SQL标准GROUP BY 子句不能跟复杂表达式，也不是跟alias，只能是实际的列名？

GPS 发表于 25-8-2009 15:58:51

SELECT
MIN(x) AS x0,
SUM( (CASE WHEN y=0 THEN 1 ELSE 0 END) ) AS y0,
SUM( (CASE WHEN y=1 THEN 1 ELSE 0 END) ) AS y1,
SUM( (CASE WHEN y=2 THEN 1 ELSE 0 END) ) AS y2,
SUM( (CASE WHEN y=3 THEN 1 ELSE 0 END) ) AS y3
FROM temptest1
GROUP BY ( (x-1)/40)
ORDER BY x0

GPS 发表于 25-8-2009 16:08:53

SQL92,好像是draft
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

section 7.7 - general rules :
2) Every row of a given group contains equal values of a given
         grouping column. When a <search condition> or <value expression>
         is applied to a group, a reference to a grouping column is a
         reference to that value.

key 发表于 25-8-2009 20:44:29

这个方案看起来很漂亮，但在sqlite3上测试不成功

原帖由 coredump 于 24-8-2009 23:34 发表 http://www.freeoz.org/forum/images/common/back.gif
SELECT
a, count(y = 0), count(y=1), count(y=2), count(y=3)
FROM
(SELECT (x/40 + 1) as a, y FROM TABLENAME)
GROUP BY a;

其中count(y=0)这样是不是标准用法那不准，如果不是还得用别的方法写

key 发表于 25-8-2009 20:47:25

在sqlite3上测试decode不支持，又可惜了一个方案

原帖由 coredump 于 24-8-2009 23:59 发表 http://www.freeoz.org/forum/images/common/back.gif
使用DECODE函数的写法(适合ORACLE,是否标准不知),我认为这个题目主要考察如何根据一个列的值生成新的列

SELECT
a, count(y0), count(y1), count(y2), count(y3)
FROM
(SELECT
(x/40 + 1) as a,
...

key 发表于 25-8-2009 20:50:29

回复 #6 coredump 的帖子

这个方案成功！

key 发表于 25-8-2009 20:54:58

这个方案很好很强大！谢谢！

原帖由 GPS 于 25-8-2009 14:58 发表 http://www.freeoz.org/forum/images/common/back.gif
SELECT
MIN(x) AS x0,
SUM( (CASE WHEN y=0 THEN 1 ELSE 0 END) ) AS y0,
SUM( (CASE WHEN y=1 THEN 1 ELSE 0 END) ) AS y1,
SUM( (CASE WHEN y=2 THEN 1 ELSE 0 END) ) AS y2,
SUM( (CASE WHEN y= ...

key 发表于 25-8-2009 21:04:40

这其实是我做的一个背单词程序的一个数据库问题。
因为我需要把不同难度的单词按组统计在一个界面上，而数据库是sqlite3，
所以才有这么多要求。

谢谢大家啦。

嗯，这个程序是for google android的。我只是自己上班路上用，
如果谁有google android想用这个程序，我可以发binary或source code给你。
过一段时间可以考虑open source出来

yuba 发表于 25-8-2009 21:27:46

回复 #20 key 的帖子

真强

放到中国移动的商场里卖吧

zycbob 发表于 25-8-2009 21:35:15

android的手机用户现在占多少？
可以做个调查~

earthengine 发表于 25-8-2009 21:44:22

原帖由 key 于 25-8-2009 20:04 发表 http://www.freeoz.org/forum/images/common/back.gif
76883

这其实是我做的一个背单词程序的一个数据库问题。
因为我需要把不同难度的单词按组统计在一个界面上，而数据库是sqlite3，
所以才有这么多要求。

谢谢大家啦。

嗯，这个程序是for google android的 ...
你们公司招人不？本人Strong on C/C++/SQL正着急找工作，你要是肯介绍一把就好了

yuba 发表于 25-8-2009 23:09:26

原帖由 earthengine 于 25-8-2009 20:44 发表 http://www.freeoz.org/forum/images/common/back.gif
你们公司招人不？本人Strong on C/C++/SQL正着急找工作，你要是肯介绍一把就好了

本言论乃黑客入侵本机器后发出

coredump 发表于 25-8-2009 23:15:10

原帖由 earthengine 于 25-8-2009 20:44 发表 http://www.freeoz.org/forum/images/common/back.gif

你们公司招人不？本人Strong on C/C++/SQL正着急找工作，你要是肯介绍一把就好了

key兄貌似strong on Java，不是C++:P

earthengine 发表于 26-8-2009 10:14:06

原帖由 coredump 于 25-8-2009 22:15 发表 http://www.freeoz.org/forum/images/common/back.gif

key兄貌似strong on Java，不是C++:P
Java也木有问题。我在国内考了SCJP，刚报了SCJD还没考。

coredump 发表于 26-8-2009 11:23:50

回复 #26 earthengine 的帖子

那你很强悍了已经，基本上Strong on everything了，不要着急，相信你这样的能力会很快找到工作的。

BTW，澳洲毕竟是市场规模太小，昨天闲来无事，看了看美国的招聘网站，就现在这号称如此萧条的时期，Senior C++ 100K ~ 140K的工作都是一堆一堆的。

someonehappy 发表于 26-8-2009 11:41:44

还是比较困惑，感觉你们的方案都是分成40组，而不是40个一组啊。

earthengine 发表于 26-8-2009 13:53:04

原帖由 someonehappy 于 26-8-2009 10:41 发表 http://www.freeoz.org/forum/images/common/back.gif
还是比较困惑，感觉你们的方案都是分成40组，而不是40个一组啊。
上面的正确方案，都是采用抛弃余数的除法，而不是取模。请搞清楚。

someonehappy 发表于 26-8-2009 16:31:45

我都当是取模了。原来是和C里面一样。

页: [1] 2

FreeOZ论坛's Archiver