HiveSQL——用户中两人一定认识的组合数

注:参考文章:

SQL之用户中两人一定认识的组合数--HQL面试题36【快手数仓面试题】_sql面试题-快手-CSDN博客文章浏览阅读1.2k次,点赞3次,收藏12次。目录0 需求分析1 数据准备2 数据分析3 小结0 需求分析设表名:table0现有城市网吧访问数据,字段:网吧id,访客id(身份证号),上线时间,下线时间规则1、如果有两个用户在一家网吧的前后上下线时间在10分钟以内,则两人可能认识规则2、如果这两个用户在三家以上网..._sql面试题-快手https://blog.csdn.net/godlovedaniel/article/details/119155757

0 问题描述

     现有一张表table21, 里面装载城市网吧访问数据,字段:网吧id, 访客id(身份证号),上线时间、下线时间

     规则1:如果有两个用户在一家网吧的前后上线时间在10分钟内,则两人可能认识;

     规则2:如果这两个用户在三家以上网吧出现【规则1】的情况,则两人一定认识

  需求:该城市上网用户中两人一定认识的组合数

1 数据准备

create table table21(wid string,uid string,ontime string,offtime string
)
row format delimited fields terminated by '\t';insert overwrite table table21 values(1,110001,'2020-01-01 11:10:00','2020-01-01 11:15:00')
,(1,110001,'2020-01-01 11:18:00','2020-01-01 11:23:00')
,(1,110002,'2020-01-01 12:10:00','2020-01-01 13:15:00')
,(1,110001,'2020-01-01 12:11:00','2020-01-01 13:10:00')
,(1,110003,'2020-01-01 12:15:00','2020-01-01 13:15:00')
,(1,110004,'2020-01-01 12:16:00','2020-01-01 13:18:00'),(2,110001,'2020-01-02 12:10:00','2020-01-02 12:30:00')
,(2,110001,'2020-01-02 12:50:00','2020-01-02 13:05:00')
,(2,110002,'2020-01-02 12:52:00','2020-01-02 12:55:00')
,(2,110003,'2020-01-02 12:58:00','2020-01-02 13:20:00')
,(2,110004,'2020-01-02 13:00:00','2020-01-02 13:10:00'),(3,110001,'2020-01-03 12:10:00','2020-01-03 12:30:00')
,(3,110003,'2020-01-03 12:55:00','2020-01-03 13:02:00')
,(3,110001,'2020-01-03 12:50:00','2020-01-03 12:55:00')
,(3,110002,'2020-01-03 13:00:00','2020-01-03 13:01:00')
,(3,110004,'2020-01-03 12:58:00','2020-01-03 13:03:00')
,(3,110002,'2020-01-03 13:20:00','2020-01-03 13:25:00');

2  数据分析

     根据规则1和规则2,求城市上网用户中两人一定认识的组合数,就是指两两相识的组合数。对于这种两两组合数一般用自关联,通过自关联将尽可能的情况表示出来,然后按照条件筛选数据

    step1:表自关联计算,得到所有相遇的情况:(笛卡尔积)

select *
from table21 as t0
join table21 as t1;

    step2:根据规则1,得出可能的结果:

 selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid = t1.widand (abs(unix_timestamp(t0.ontime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.ontime, 'yyyy-MM-dd HH:mm:ss')) < 600 orabs(unix_timestamp(t0.offtime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.offtime, 'yyyy-MM-dd HH:mm:ss')) < 600)and t0.uid > t1.uid

上述代码用到的函数:

unix_timestamp(日期转时间戳函数)
语法:unix_timestamp(string date)  、unix_timestamp(string date,string pattern)
返回值:bigint
说明:将格式为"yyyy-MM-dd HH:mm:ss"的日期 转换成 unix的时间戳。如果转换失败,则返回值为0;
举例:select unix_timestamp('20240201 20:17:11','yyyyMMdd HH:mm:ss')  --> 1706825843

 abs(unix_timestamp(t0.ontime, 'yyyy-MM-dd HH:mm:ss')  - unix_timestamp(t1.ontime, 'yyyy-MM-dd HH:mm:ss')) < 600  代表的意思是:两个用户在一家网吧的前后上线时间在10分钟内(10分钟也就是600秒)

   ps: 需要将同一网吧中可能两两相识的人筛选出来,所以【用户A、用户B】 与【用户B、用户A】 实际上是一样的,只需要选出 t0.uid > t1.uid 即可(去重取一)

step3:根据step2,可以将同一网吧中可能两两相识的人筛选出来,将互相认识的人组合成一个key,通过该key来判断该两人是否满足规则2。具体sql如下:

 selectt0_wid,-- 将可能互相认识的人的uid拼接起来,组成key值(uuid)concat_ws('~', t0_uid, t1_uid) as uuid
from (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid = t1.widand (abs(unix_timestamp(t0.ontime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.ontime, 'yyyy-MM-dd HH:mm:ss')) < 600 orabs(unix_timestamp(t0.offtime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.offtime, 'yyyy-MM-dd HH:mm:ss')) < 600)and t0.uid > t1.uid) t2

step4:对【两人一定认识】记录进行打标签,记为 1

selectuuid,-- 对【两人一定认识】记录进行打标签,记为 1if(count(t0_wid) >=3,1,0) as flagfrom
(selectt0_wid,-- 将可能互相认识的人的uid拼接起来,组成key值(uuid)concat_ws('~', t0_uid, t1_uid) as uuid
from (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid = t1.widand (abs(unix_timestamp(t0.ontime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.ontime, 'yyyy-MM-dd HH:mm:ss')) < 600 orabs(unix_timestamp(t0.offtime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.offtime, 'yyyy-MM-dd HH:mm:ss')) < 600)and t0.uid > t1.uid) t2
)t3
group by  uuid;

step4:计算满足规则1和规则2的记录总数,得出结果为6条

selectcount(1) as cnt
from (selectuuid,-- 对【两人一定认识】记录进行打标签,记为 1if(count(t0_wid) >= 3, 1, 0) as flagfrom (selectt0_wid,-- 将可能互相认识的人的uid拼接起来,组成key值(uuid)concat_ws('~', t0_uid, t1_uid) as uuidfrom (selectt0.wid as t0_wid,t0.uid as t0_uid,t1.wid as t1_wid,t1.uid as t1_uidfrom table21 as t0join table21 as t1where t0.wid = t1.widand (abs(unix_timestamp(t0.ontime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.ontime, 'yyyy-MM-dd HH:mm:ss')) < 600 orabs(unix_timestamp(t0.offtime, 'yyyy-MM-dd HH:mm:ss')- unix_timestamp(t1.offtime, 'yyyy-MM-dd HH:mm:ss')) < 600)and t0.uid > t1.uid) t2) t3group by uuid) t4;

3 小结  

   本案例题型属于:“共同xx”,例如:共同好友、互相认识、共同使用等。遇到这类关键字的时候,往往可以采用自关联的方式解决。(笛卡尔积:“一对多”或者“ 多对一”),一般的解题步骤就是:通过自关联将所有的组合求解出来,然后将符合条件的数据进行过滤即可。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/254317.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

JRebel激活-nginx版本

nginx转发流量&#xff08;代替其他网上说的那个工具&#xff09; proxy_pass http://idea.lanyus.com; 工具激活 填写内容说明&#xff1a; 第一行的激活网址是&#xff1a;http://127.0.0.1:8888/ 正确的GUID。GUID 可以通过专门的网站来生成&#xff08;点击打开&#…

Vue3快速上手(一)使用vite创建项目

一、准备 在此之前&#xff0c;你的电脑&#xff0c;需要安装node.js,我这边v18.19.0 wangdymb 2024code % node -v v18.19.0二、创建 执行npm create vuelatest命令即可使用vite创建vue3项目 有的同学可能卡主不动&#xff0c;可能是npm的registry设置的问题 先看下&#x…

Qt PCL学习(二):点云读取与保存

注意事项 版本一览&#xff1a;Qt 5.15.2 PCL 1.12.1 VTK 9.1.0前置内容&#xff1a;Qt PCL学习&#xff08;一&#xff09;&#xff1a;环境搭建 0. 效果演示 1. pcl_open_save.pro QT core guigreaterThan(QT_MAJOR_VERSION, 4): QT widgets// 添加下行代码&#…

npm 下载报错

报错信息 : 证书过期 (CERT_HAS_EXPIRED) D:\Apps\nodejs-v18.16.1\npx.cmd --yes create-next-app"latest" . --ts npm ERR! code CERT_HAS_EXPIRED npm ERR! errno CERT_HAS_EXPIRED npm ERR! request to https://registry.npm.taobao.org/create-next-app failed…

7机器人位姿的数学描述与坐标变

由上次刚体的空间转动直接切换为机器人相关术语。 1.机器人位姿的数学描述与坐标变换 1.1位姿描述 {B}相对于{A}的姿态描述用3x3矩阵表示为&#xff1a; 式中为三个单位正交主矢量&#xff0c;分别表示刚体坐标系{B}的三个坐标轴XBYBZB在参考系{A}中的方位&#xff0c;∠XBXA表…

Git 消除对某个文件的追踪

参考&#xff1a; &#xff08;尚未验证他的该方法&#xff09; https://www.golinuxcloud.com/git-remove-file-from-tracking/https://www.golinuxcloud.com/git-remove-file-from-tracking/

第4节、电机多段转动【51单片机+L298N步进电机系列教程】

↑↑↑点击上方【目录】&#xff0c;查看本系列全部文章 摘要&#xff1a;本节介绍用控制步进电机三个主要参数角度、速度、方向&#xff0c;实现简单的步进电机多段控制 一、目标功能 输入多个目标角度&#xff0c;以及每个角度对应的速度&#xff0c;实现步进电机的多段多速…

TCP/IP协议以及UDP(超详细,看这一篇就够了)

&#x1f493; 博客主页&#xff1a;从零开始的-CodeNinja之路 ⏩ 收录专栏&#xff1a;TCP/IP协议以及UDP(超详细,看这一篇就够了) &#x1f389;欢迎大家点赞&#x1f44d;评论&#x1f4dd;收藏⭐文章 TCP/IP协议以及UDP(超详细,看这一篇就够了 前提概括接收端和发送端客户…

【数据分享】1929-2023年全球站点的逐月平均能见度(Shp\Excel\免费获取)

气象数据是在各项研究中都经常使用的数据&#xff0c;气象指标包括气温、风速、降水、能见度等指标&#xff0c;说到气象数据&#xff0c;最详细的气象数据是具体到气象监测站点的数据&#xff01; 之前我们分享过1929-2023年全球气象站点的逐月平均气温数据、逐月最高气温数据…

CSP-202112-1-序列查询

CSP-202112-1-序列查询 提示&#xff1a;若存在区间[i,j) 满足&#xff1a;f(i)f(i1)…f(j-1)&#xff0c;使用乘法运算 f(i)x(j-i)代替将 f()到 f(j- 1)逐个相加,或可大幅提高算法效率。 一定要看提示&#xff01;单纯的模拟时间会超限&#xff01;算法也是根据提示设计的。 …

【Web】vulhub Fastjson反序列化漏洞复现学习笔记

目录 1.2.24 RCE CVE-2017-18349 复现流程 原理分析 1.2.47 RCE CNVD-2019-22238 复现流程 原理分析 漏洞探测 1.2.24 RCE CVE-2017-18349 复现流程 vulhub启动靶场 用marshalsec启动LDAP/RMI服务 java -cp marshalsec-0.0.3-SNAPSHOT-all.jar marshalsec.jndi.LDAPRef…

【每日一题】LeetCode——链表的中间结点

&#x1f4da;博客主页&#xff1a;爱敲代码的小杨. ✨专栏&#xff1a;《Java SE语法》 | 《数据结构与算法》 | 《C生万物》 ❤️感谢大家点赞&#x1f44d;&#x1f3fb;收藏⭐评论✍&#x1f3fb;&#xff0c;您的三连就是我持续更新的动力❤️ &#x1f64f;小杨水平有…

LabVIEW网络测控系统

LabVIEW网络测控系统 介绍了基于LabVIEW的网络测控系统的开发与应用&#xff0c;通过网络技术实现了远程的数据采集、监控和控制。系统采用LabVIEW软件与网络通信技术相结合&#xff0c;提高了系统的灵活性和扩展性&#xff0c;适用于各种工业和科研领域的远程测控需求。 随着…

机器学习:回归决策树(Python)

一、平方误差的计算 square_error_utils.py import numpy as npclass SquareErrorUtils:"""平方误差最小化准则&#xff0c;选择其中最优的一个作为切分点对特征属性进行分箱处理"""staticmethoddef _set_sample_weight(sample_weight, n_samp…

FlinkSql 窗口函数

Windowing TVF 以前用的是Grouped Window Functions&#xff08;分组窗口函数&#xff09;&#xff0c;但是分组窗口函数只支持窗口聚合 现在FlinkSql统一都是用的是Windowing TVFs&#xff08;窗口表值函数&#xff09;&#xff0c;Windowing TVFs更符合 SQL 标准且更加强大…

第5章 数据库操作

学习目标 了解数据库&#xff0c;能够说出数据库的概念、特点和分类 熟悉Flask-SQLAlchemy的安装&#xff0c;能够在Flask程序中独立安装扩展包Flask-SQLAlchemy 掌握数据库的连接方式&#xff0c;能够通过设置配置项SQLALCHEMY_DATABASE_URI的方式连接数据库 掌握模型的定义…

rust语言tokio库底层原理解析

目录 1 rust版本及tokio版本说明1 tokio简介2 tokio::main2.1 tokio::main使用多线程模式2.2 tokio::main使用单线程模式 3 builder.build()函数3.1 build_threaded_runtime()函数新的改变功能快捷键合理的创建标题&#xff0c;有助于目录的生成如何改变文本的样式插入链接与图…

【开源】JAVA+Vue.js实现高校实验室管理系统

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、研究内容2.1 实验室类型模块2.2 实验室模块2.3 实验管理模块2.4 实验设备模块2.5 实验订单模块 三、系统设计3.1 用例设计3.2 数据库设计 四、系统展示五、样例代码5.1 查询实验室设备5.2 实验放号5.3 实验预定 六、免责说明 一、摘…

使用 Docker 镜像预热提升容器启动效率详解

概要 在容器化部署中,Docker 镜像的加载速度直接影响到服务的启动时间和扩展效率。本文将深入探讨 Docker 镜像预热的概念、必要性以及实现方法。通过详细的操作示例和实践建议,读者将了解如何有效地实现镜像预热,以加快容器启动速度,提高服务的响应能力。 Docker 镜像预热…

【数据结构】堆(创建,调整,插入,删除,运用)

目录 堆的概念&#xff1a; 堆的性质&#xff1a; 堆的存储方式&#xff1a; 堆的创建 &#xff1a; 堆的调整&#xff1a; 向下调整&#xff1a; 向上调整&#xff1a; 堆的创建&#xff1a; 建堆的时间复杂度&#xff1a; 向下调整&#xff1a; 向上调整&#xff…