#58 SQL in JS(LINQ版本)


  • 0
    administrators

    在 JavaScript 中实现类似 LINQ 风格的数据查询语法。具体来说,就是用:

    • from() 表示数据源,可以有多个不同的数据源,将会进行笛卡尔乘积
    • select() 表示数据投影(选择)
    • where() 表示过滤条件,可以组合多个,多个 where 之间是 AND 连接,where 中多个参数是 OR 连接的
    • groupBy() 表示分组条件(相同结果就会合成一组,可以同时指定好多分组条件,结果将是嵌套分组的)
    • having() 表示从分组结果中进行选择(选出特定的分组)类似与 where

    以上所有函数都需要链式调用风格,但是无需考虑中间断开保存为某个变量的情形,因此放心的用闭包即可。

    使用例子:

    const somenumbers = [1, 2, 3]
    query().select().from(somenumbers).execute(); //[1, 2, 3]
    query().from(somenumbers).select().execute(); //[1, 2, 3] execute前顺序无关哦
    
    //同样也适用于对象数组
    var persons = [
      {name: '彼得', profession: '教师', age: 20, maritalStatus: '已婚'},
      {name: '迈克尔', profession: '教师', age: 50, maritalStatus: '未婚'},
      {name: '彼得', profession: '教师', age: 20, maritalStatus: '已婚'},
      {name: '安娜', profession: '科学家', age: 20, maritalStatus: '已婚'},
      {name: '露丝', profession: '科学家', age: 50, maritalStatus: '已婚'},
      {name: '安娜', profession: '科学家', age: 20, maritalStatus: '未婚'},
      {name: '安娜', profession: '政治家', age: 50, maritalStatus: '已婚'}
    ];
    query().select().from(persons).execute(); // [{name: '彼得',...}, {name: '迈克尔', ...}]
    
    function profession(person) {
      return person.profession;
    }
    
    //也可以做一些投影操作
    query().select(profession).from(persons).execute(); //["教师", "教师", "教师", "科学家", "科学家", "科学家", "政治家"]
    
    //除了where和having之外,其他的操作不允许重复写
    query().select().select().execute(); //Error('Duplicate SELECT');
    query().select().from([]).select().execute(); //Error('Duplicate SELECT');
    query().select().from([]).from([]).execute(); //Error('Duplicate FROM');
    query().select().from([]).where([]).where([]) //这表示AND连接两个条件
    
    //你可以省略execute前的任何一部分
    query().select().execute(); //[]
    query().from(somenumbers).execute(); // [1, 2, 3]
    query().execute(); // []
    
    //需要支持where语法
    function isTeacher(person) {
      return person.profession === 'teacher';
    }
    
    //SELECT profession FROM persons WHERE profession="teacher" 
    query().select(profession).from(persons).where(isTeacher).execute(); //["教师", "教师", "教师"]
    
    //SELECT * FROM persons WHERE profession="teacher" 
    query().select().from(persons).where(isTeacher).execute(); //[{person: '彼得', profession: '教师', ...}, ...]
    
    function name(person) {
      return person.name;
    }
    
    //SELECT name FROM persons WHERE profession="teacher" 
    query().select(name).from(persons).where(isTeacher).execute();//["彼得", "迈克尔", "彼得"]
    
    //还需要支持groupBy语法
    //SELECT * FROM persons GROUP BY profession <- Bad in SQL but possible in this kata
    query().select().from(persons).groupBy(profession).execute(); 
    [
      ["教师",
         [
           {
            name: "彼得",
            profession: "教师"
            ...
          },
          {
            name: "迈克尔",
            profession: "教师"
            ...
          }
        ]
      ],
      ["科学家",
        [
           {
              name: "安娜",
              profession: "科学家"
            },
         ...
       ]
      ]
      ...
    ]
    //where和groupBy可以同时存在
    //SELECT * FROM persons WHERE profession='teacher' GROUP BY profession
    query().select().from(persons).where(isTeacher).groupBy(profession).execute();
    
    //或者,你可能需要select(下面这个例子演示了如何模拟select unique)
    function professionGroup(group) {
      return group[0];
    }
    
    //SELECT profession FROM persons GROUP BY profession
    query().select(professionGroup).from(persons).groupBy(profession).execute(); //["教师","科学家","政治家"]
    
    //分组不一定要从对象数组里分
    function isEven(number) {
      return number % 2 === 0;
    }
    
    function parity(number) {
      return isEven(number) ? '偶数' : '奇数';
    }
    
    const numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9]; 
    
    //SELECT * FROM numbers
    query().select().from(numbers).execute(); //[1, 2, 3, 4, 5, 6, 7, 8, 9]
    
    //SELECT * FROM numbers GROUP BY parity
    query().select().from(numbers).groupBy(parity).execute(); //[["奇数",[1,3,5,7,9]],["偶数",[2,4,6,8]]]
    
    //你甚至可以嵌套多个分组
    function isPrime(number) {
      if (number < 2) {
        return false;
      }
      var divisor = 2;
      for(; number % divisor !== 0; divisor++);
      return divisor === number;
    }
    
    function prime(number) {
      return isPrime(number) ? '素数' : '合数';
    }
    
    //SELECT * FROM numbers GROUP BY parity, isPrime
    query().select().from(numbers).groupBy(parity, prime).execute(); // [["奇数",[["合数",[1,9]],["素数",[3,5,7]]]],["偶数",[["素数",[2]],["合数",[4,6,8]]]]]
    
    //都有groupby了,怎么能少了having
    function odd(group) {
      return group[0] === 'odd';
    }
    
    query().select().from(numbers).groupBy(parity).having(odd).execute(); //[["奇数",[1,3,5,7,9]]]
    
    //排序也是很重要滴
    
    function descendentCompare(number1, number2) {
      return number2 - number1;
    }
    
    //SELECT * FROM numbers ORDER BY value DESC 
    query().select().from(numbers).orderBy(descendentCompare).execute(); //[9,8,7,6,5,4,3,2,1]
     
    //from需要支持多个不同来源
    var teachers = [
      {
        teacherId: '1',
        teacherName: '彼得'
      },
      {
        teacherId: '2',
        teacherName: '安娜'
      }
    ];
    
    
    var students = [
      {
        studentName: '迈克尔',
        tutor: '1'
      },
      {
        studentName: '露丝',
        tutor: '2'
      }
    ];
    
    function teacherJoin(join) {
      return join[0].teacherId === join[1].tutor;
    }
    
    function student(join) {
      return {studentName: join[1].studentName, teacherName: join[0].teacherName};
    }
    
    //SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor
    query().select(student).from(teachers, students).where(teacherJoin).execute(); //[{"studentName":"迈克尔","teacherName":"彼得"},{"studentName":"露丝","teacherName":"安娜"}]
    
    //where和having可以支持多个参数,表示OR连接
    function tutor1(join) {
      return join[1].tutor === "1";
    }
    
    //SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor AND tutor = 1 
    query().select(student).from(teachers, students).where(teacherJoin).where(tutor1).execute(); //[{"studentName":"迈克尔","teacherName":"彼得"}] <- AND 连接
    
    function lessThan3(number) {
      return number < 3;
    }
    
    function greaterThan4(number) {
      return number > 4;
    }
    
    //SELECT * FROM number WHERE number < 3 OR number > 4
    query().select().from(numbers).where(lessThan3, greaterThan4).execute(); //[1, 2, 5, 7] <- OR连接
    
    function greatThan1(group) {
      return group[1].length > 1;
    }
    
    function isPair(group) {
      return group[0] % 2 === 0;
    }
    
    function id(value) {
      return value;
    }
    
    function frequency(group) {
      return { value: group[0], frequency: group[1].length };      
    }
    
    //SELECT number, count(number) FROM numbers GROUP BY number HAVING count(number) > 1 AND isPair(number)
    query().select(frequency).from(numbers).groupBy(id).having(greatThan1).having(isPair).execute(); // [{"value":2,"frequency":2},{"value":6,"frequency":2}])
    

  • 0

    from里,多个数据源的 "笛卡尔乘积"不是很理解,可以解释下嘛?


  • 0
    administrators

    @Jonham 可以问问 @CodeHz :P


  • 2

    @Jonham 比如数据源[1,2,3]和[4,5]
    那么联合起来就是
    [1,4]
    [1,5]
    [2,4]
    [2,5]
    [3,4]
    [3,5]
    这样。。。可以看笛卡儿积,还有就是关系代数中的原始运算那一节,
    实际上这里应该是连接操作,但是在这里不太好抽象,而笛卡儿积比较容易抽象一点。。例子的话,可以在题干中搜索query().select(student).from(teachers, students).where(teacherJoin).where(tutor1).execute()这一句


  • 0

    @CodeHz 懂了。有点全排列的意思。


  • 0

    @CodeHz #奥义·四重分组失败# 分组两组通过了,但是四组的通不过。
    可以给个思路吗?

    我用了 递归的方法,每次处理上一次分组后的数组。


  • 0

    @Jonham 递归应该是没问题的。。
    4重分组的结果应该是类似于这样的形式,以下按照专业, 姓名, 年龄, 婚姻状态进行分组

    [["教师",[["彼得",[[20,[["已婚",[{"name":"彼得","profession":"教师","age":20,"maritalStatus":"已婚"},{"name":"彼得","profession":"教师","age":20,"maritalStatus":"已婚"}]]]]]],["迈克尔",[[50,[["未婚",[{"name":"迈克尔","profession":"教师","age":50,"maritalStatus":"未婚"}]]]]]]]],["科学家",[["安娜",[[20,[["已婚",[{"name":"安娜","profession":"科学家","age":20,"maritalStatus":"已婚"}]],["未婚",[{"name":"安娜","profession":"科学家","age":20,"maritalStatus":"未婚"}]]]]]],["露丝",[[50,[["已婚",[{"name":"露丝","profession":"科学家","age":50,"maritalStatus":"已婚"}]]]]]]]],["政治家",[["安娜",[[50,[["已婚",[{"name":"安娜","profession":"政治家","age":50,"maritalStatus":"已婚"}]]]]]]]]]
    

    0_1497415128669_upload-773eebf4-ce76-45d6-b60c-b9e270f9e7ed


  • 0

    @CodeHz 谢谢!我应该递归中的方法没写好,晚一点看下。


  • 0

    题目的例子里感觉有一堆错误。。。
    无法理解
    比如

    const numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9]; 
    //SELECT * FROM number WHERE number < 3 OR number > 4
    query().select().from(numbers).where(lessThan3, greaterThan4).execute(); //[1, 2, 5, 7] <- OR连接
    

    1-9中大于4或者小于3,怎么会只有1、2、5、7

    ==================================
    这题不是特别困难
    是特别烦。。。
    groupBy不用递归真心无力


  • 0
    administrators

    @elfish 这个可以 cc 一下 @CodeHz :P


  • 0

    const query = (()=>{
    //{方法名:{'once':执行限制,'index':执行顺序,'build':转换函数}}
        var m={'from':{'once':true,'index':0,'build':dataArr=>arr=>arr.concat(
                dataArr.length<2?[].concat(...dataArr):
                dataArr.reduce(
                    (data,arr)=>[].concat(...data.map(v=>arr.map(a=>v.concat([a])))),
                [[]]))},
            'where':{'once':false,'index':1,'build':fArr=>arr=>arr.filter(v=>fArr.some(f=>f(v)))},
            /*'groupBy':{'once':true,'index':2,'build':fArr=>arr=>
                (fun=>fun(fun))(f1=>f2=>(v1,v2,v3)=>f2(f1(f1)(f2))(v1,v2,v3))
                (group=>(funArr,data,fid=0)=>
                    (f=>f?[...new Set(data.map(v=>f(v)))].map(
                        tag=>[tag,group(funArr,data.filter(v=>f(v)==tag),fid+1)]
                    ):data)(funArr[fid])
                )(fArr,arr)},*/
            'groupBy':{'once':true,'index':2,'build':fArr=>arr=>
                arr.reduce((a,v)=>(fArr.reduce((items,f)=>(
                    (items.find(item=>item[0]==f(v)) || (items[items.length]=[f(v),[]]))[1]
                ),a).push(v),a),[])},
            'having':{'once':false,'index':3,'build':fArr=>arr=>arr.filter(v=>fArr.some(f=>f(v)))},
            'orderBy':{'once':true,'index':4,'build':fArr=>arr=>fArr.reduceRight((a,f)=>a.sort(f),arr)},
            'select':{'once':true,'index':5,'build':fArr=>arr=>fArr.reduce((a,f)=>a.map(f),arr)}};
    /*
        使用【【方法名1,参数1】,【方法名2,参数2】。。。】形式保存一个指令列表
        对指令排序
        将参数转换为数据操作函数
        依次处理初始数据
    */
    //输入一个原始指令列表
        return (oldDo=[]) => {
    //返回对象
            return new(function(){
    //初始化指令列表
                var doArr=oldDo.concat();
    //循环设定对象方法
                for(let method in m){
                    this[method]=(...dataArr)=>{
    //检查方法执行次数限制 && 指令列表中是否已存在
                        if(m[method].once && doArr.some(x=>x[0]==method)){
    //初始化指令列表
                            doArr=oldDo.concat();
    //抛出错误
                            throw new Error('Duplicate '+method.toUpperCase());
                        }
    //将【方法名,参数】放入指令列表
                        doArr.push([method,dataArr]);
    //返回对象以便链式调用
                        return this;
                    }
                }
    //执行数据操作
                this.execute=()=>{
                    let ret=doArr;
    //初始化指令列表
                    doArr=oldDo.concat();
    //按设置index对指令进行排序
                    return ret.sort((a,b)=>m[a[0]].index-m[b[0]].index)
    //检查指令参数是否存在,
    //不存在:直接返回输入数据,
    //存在:从设置中取出对应转换函数,
    //     使用转换函数将参数转换为数据操作函数,
    //     返回操作后的数据
                        .reduce((d,f)=>f[1].length?m[f[0]].build(f[1])(d):d,[])
                }
    //克隆函数
    //产生一个当前对象副本,并初始化指令列表
                this.clone=()=>{
                    let ret=doArr;
    //初始化指令列表
                    doArr=oldDo.concat();
    //返回副本
                    return query(ret);
                };
    //调试属性,提供内部指令列表,可手工修改
                this.debug={'doArr':doArr,'oldDo':oldDo};
            }
        )()}
    })()
    

    groupBy告别递归神清气爽XD
    我觉得这题我已经通关了。。。OTZ


  • 1

    @ScriptOJ 看完答案,我觉得我最终优化比答案更赞

    const query = (()=>{
        const m={'from':{'once':true,'index':0,'mReduce':(dataArr,arr)=>arr.concat(
                dataArr.length<2?[].concat(...dataArr):
                dataArr.reduce(
                    (data,arr)=>[].concat(...data.map(v=>arr.map(a=>v.concat([a])))),
                [[]]))},
            'where':{'once':false,'index':1,'mReduce':(fArr,arr)=>arr.filter(v=>fArr.some(f=>f(v)))},
            'groupBy':{'once':true,'index':2,'mReduce':(fArr,arr)=>
                arr.reduce((a,v)=>(fArr.reduce((items,f)=>(
                    (items.find(item=>item[0]==f(v)) || (items[items.length]=[f(v),[]]))[1]
                ),a).push(v),a),[])},
            'having':{'once':false,'index':3,'mReduce':(fArr,arr)=>arr.filter(v=>fArr.some(f=>f(v)))},
            'orderBy':{'once':true,'index':4,'mReduce':(fArr,arr)=>fArr.reduceRight((a,f)=>a.sort(f),arr)},
            'select':{'once':true,'index':5,'mReduce':(fArr,arr)=>fArr.reduce((a,f)=>a.map(f),arr)}};
        return () => 
            new(function(){
                var doArr=[];
                for(let method in m)
                    this[method]=(...dataArr)=>{
                        if(m[method].once && doArr.some(x=>x[0]==method))
                            throw new Error('Duplicate '+method.toUpperCase());
                        doArr.push([method,dataArr]);
                        return this;
                    }
                this.execute=()=>
                    doArr.sort((a,b)=>m[a[0]].index-m[b[0]].index)
                        .reduce((d,f)=>f[1].length?m[f[0]].mReduce(f[1],d):d,[])
            })()
    })()
    

登录后回复
 

与 ScriptOJ 的连接断开,我们正在尝试重连,请耐心等待