群里一个数据库讨论题

key

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

有一个数据库，有字段 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

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

coredump

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

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

coredump

使用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

刚才查了下SQL 92标准，发现DECODE不是标准，对应的符合SQL92的应该使用CASE表达式(6.9  case 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

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

周星星1832

没看懂题

yuba

题目和答案都没看懂

someonehappy

题目里面有个地方没讲清楚吧
数据分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

原帖由 someonehappy 于 25-8-2009 11:27 发表

                               
登录/注册后可看大图

题目里面有个地方没讲清楚吧
数据分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

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

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

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

GPS

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

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

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

原帖由 coredump 于 24-8-2009 23:34 发表

                               
登录/注册后可看大图

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

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

原帖由 coredump 于 24-8-2009 23:59 发表

                               
登录/注册后可看大图

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

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

key

这个方案成功！

key

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

原帖由 GPS 于 25-8-2009 14:58 发表

                               
登录/注册后可看大图

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

登录/注册后可看大图

yuba

真强

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

zycbob

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

earthengine

原帖由 key 于 25-8-2009 20:04 发表

                               
登录/注册后可看大图

76883

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

谢谢大家啦。

嗯，这个程序是for google android的 ...

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

yuba

原帖由 earthengine 于 25-8-2009 20:44 发表

                               
登录/注册后可看大图

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

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

coredump

原帖由 earthengine 于 25-8-2009 20:44 发表

                               
登录/注册后可看大图

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

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

earthengine

原帖由 coredump 于 25-8-2009 22:15 发表

                               
登录/注册后可看大图

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

Java也木有问题。我在国内考了SCJP，刚报了SCJD还没考。

coredump

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

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

someonehappy

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

earthengine

原帖由 someonehappy 于 26-8-2009 10:41 发表

                               
登录/注册后可看大图

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

上面的正确方案，都是采用抛弃余数的除法，而不是取模。请搞清楚。

someonehappy

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