#58 SQL in JS(LINQ版本)


  • 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不用递归真心无力


  • 1
    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


  • 3

    @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,[])
            })()
    })()
    

  • 0

    卡了好几个小时 , 终于过了 , 原因竟然是 from 传进来的参数可能不是一个数组... 是一个类数组

    然后一直报 SELECT * FROM number WHERE number < 3 OR number > 4 失败 这种错误信息... 太坑了...


  • 0

    0_1549852201462_#58 SQL in JS(LINQ版本).png
    希望能对你有帮助吧.


  • 0

    const cartesianProductOf = data =>
      data.length <= 1
        ? Array.from(data[0])
        : data.reduce(
            (r, a) =>
              (k => (r.forEach(x => a.forEach(b => k.push([...x, b]))), k))([]),
            [[]]
          )
    const handleGroup = (index, groupFn, data, fn = groupFn[index]) =>
      fn
        ? data
            .reduce(
              (r, v) =>
                ((k, y = r.find(([f]) => f === k)) => (
                  y ? y[1].push(v) : r.push([k, [v]]), r
                ))(fn(v)),
              []
            )
            .map(v => ((v[1] = handleGroup(index + 1, groupFn, v[1])), v))
        : data
    
    const query = () => {
      const LINQ = {
        from(...data) {
          this.data = cartesianProductOf(data)
          return this
        },
        where(...fns) {
          this.whereFn ? this.whereFn.push(fns) : (this.whereFn = [fns])
          return this
        },
        groupBy(...fns) {
          this.groupFn = fns
          return this
        },
        having(...fns) {
          this.havingFn ? this.havingFn.push(fns) : (this.havingFn = [fns])
          return this
        },
        select(selectFn = _ => _) {
          this.selectFn = selectFn
          return this
        },
        orderBy(fn) {
          this.orderFn = fn
          return this
        },
        execute() {
          if (!this.data) {
            this.data = []
          }
          if (this.whereFn) {
            this.whereFn.forEach(fns => {
              this.data = this.data.filter(data => fns.some(fn => fn(data)))
            })
          }
          if (this.groupFn) {
            this.data = handleGroup(0, this.groupFn, this.data)
          }
          if (this.havingFn) {
            this.havingFn.forEach(fns => {
              this.data = this.data.filter(data => fns.some(fn => fn(data)))
            })
          }
          if (this.orderFn) {
            this.data = this.data.sort(this.orderFn)
          }
          if (this.selectFn) {
            this.data = this.data.map(this.selectFn)
          }
    
          return this.data
        }
      }
    
      const cantDuplicateProps = ['FROM', 'GROUPBY', 'SELECT', 'ORDERBY']
      return new Proxy(LINQ, {
        get(target, key) {
          if (cantDuplicateProps.includes(key.toUpperCase())) {
            if (target[key].haveBeenCalled) {
              throw new Error(`Duplicate ${key.toUpperCase()}`)
            }
            target[key].haveBeenCalled = true
          }
          return Reflect.get(target, key)
        }
      })
    }
    
    

  • 0

    测试文件,格式乱了.

    const problem = '#58 SQL in JS(LINQ版本)'
    const query = require(`../ScriptOJ/${problem}`)
    
    describe(problem, () => {
    	const somenumbers = [1, 2, 3]
    	let 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: '已婚' }
    	]
    	const numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9]
    	function profession(person) {
    		return person.profession
    	}
    	function name(person) {
    		return person.name
    	}
    	function age(person) {
    		return person.age
    	}
    	function maritalStatus(person) {
    		return person.maritalStatus
    	}
    	function isTeacher(person) {
    		return person.profession === '教师'
    	}
    	function isEven(number) {
    		return number % 2 === 0
    	}
    
    	function parity(number) {
    		return isEven(number) ? '偶数' : '奇数'
    	}
    	function isPrime(number) {
    		if (number < 2) {
    			return false
    		}
    		let divisor = 2
    		for (; number % divisor !== 0; divisor++){}
    		return divisor === number
    	}
    
    	function prime(number) {
    		return isPrime(number) ? '素数' : '合数'
    	}
    	function odd(group) {
    		return group[0] === '奇数'
    	}
    	function descendentCompare(number1, number2) {
    		return number2 - number1
    	}
    	let teachers = [ { teacherId: '1', teacherName: '彼得' }, { teacherId: '2', teacherName: '安娜' } ]
    	let 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 }
    	}
    	function tutor1(join) {
    		return join[1].tutor === '1'
    	}
    	function lessThan3(number) {
    		return number < 3
    	}
    
    	function greaterThan4(number) {
    		return number > 4
    	}
    	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 }
    	}
    	test('base-test',()=>{
    		expect(query().select().from(somenumbers).execute()).toEqual(somenumbers)
    		expect(query().from(somenumbers).select().execute()).toEqual(somenumbers)
    		expect(query().select().from(persons).execute()).toEqual(persons)
    		expect(query().select().from(numbers).execute()).toEqual(numbers)
    	})
    	test('select-test',()=>{
    		expect(query().select(profession).from(persons).execute()).toEqual(['教师', '教师', '教师', '科学家', '科学家', '科学家', '政治家'])
    	})
    	test('duplicate-test',()=>{
    		expect(() => query().select().select().execute()).toThrow(new Error('Duplicate SELECT'))
    		expect(() => query().select().from([]).from([]).execute()).toThrow(new Error('Duplicate FROM'))
    		expect(query().select().from([]).where([]).where([])).toHaveProperty('execute')
    	})
    	test('omit-test',()=>{
    		expect(query().select().execute()).toEqual([])
    		expect(query().from(somenumbers).execute()).toEqual(somenumbers)
    		expect(query().execute()).toEqual([])
    	})
    	test('where-test',()=>{
    		expect(query().select(profession).from(persons).where(isTeacher).execute()).toEqual(['教师', '教师', '教师'])
    		expect(query().select().from(persons).where(isTeacher).execute()).toEqual([
    			{ name: '彼得', profession: '教师', age: 20, maritalStatus: '已婚' },
    			{ name: '迈克尔', profession: '教师', age: 50, maritalStatus: '未婚' },
    			{ name: '彼得', profession: '教师', age: 20, maritalStatus: '已婚' }])
    		expect(query().select(name).from(persons).where(isTeacher).execute()).toEqual(['彼得', '迈克尔', '彼得'])
    		expect(query().select().from(numbers).where(lessThan3, greaterThan4).execute()).toEqual([1, 2, 5,6,7,8,9])
    	})
    	test('group-test',()=>{
    		expect(query().select().from(persons).groupBy(profession).execute()).toEqual(
    			[
    				['教师',
    					[
    						{ 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: '已婚' } ]]
    			]
    		)
    		expect(query().select().from(numbers).groupBy(parity).execute()).toEqual([['奇数', [1, 3, 5, 7, 9]], ['偶数', [2, 4, 6, 8]]])
    		expect(query().select().from(numbers).groupBy(parity, prime).execute()).toEqual([['奇数', [['合数', [1, 9]], ['素数', [3, 5, 7]]]], ['偶数', [['素数', [2]], ['合数', [4, 6, 8]]]]])
    		expect(query().select().from(persons).groupBy(profession,name,age,maritalStatus).execute()).toEqual([['教师',[['彼得',[[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':'已婚'}]]]]]]]]])
    	})
    	test('having-test',()=>{
    		expect(query().select().from(numbers).groupBy(parity).having(odd).execute()).toEqual([['奇数', [1, 3, 5, 7, 9]]])
    		expect(query().select(frequency).from(numbers).groupBy(id).having(greatThan1).having(isPair).execute()).toEqual([])
    	})
    	test('order-test',()=>{
    		expect(query().select().from(numbers).orderBy(descendentCompare).execute()).toEqual([9, 8, 7, 6, 5, 4, 3, 2, 1])
    	})
    	test('multiTables -test',()=>{
    		expect(query().select(student).from(teachers, students).where(teacherJoin).execute() ).toEqual([{ 'studentName': '迈克尔', 'teacherName': '彼得' }, { 'studentName': '露丝', 'teacherName': '安娜' }])
    		expect(query().select(student).from(teachers, students).where(teacherJoin).where(tutor1).execute()).toEqual([{ 'studentName': '迈克尔', 'teacherName': '彼得' }])
    	})
    })
    
    

登录后回复
 

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