preguntas de la entrevista sql 1
1 pregunta de la entrevista de Tencent
table_A (ID de usuario y hora de inicio de sesión) encuentra la cantidad de usuarios que han iniciado sesión durante 3 días consecutivos
blogs.com/ ikww/p/ 12012831.html
Consultar SQL a los usuarios que han iniciado sesión continuamente durante más de 7 días
Consultar a los usuarios que han iniciado sesión continuamente durante 7 días es un problema clásico, y Hay muchas soluciones. Aquí hablaré sobre el método del autor. Espero que ayude a todos.
Ideas específicas:
1. Debido a que un usuario puede iniciar sesión más de una vez al día, primero es necesario eliminar los duplicados de la fecha de inicio de sesión diaria del usuario.
2. Luego use la función row_number() over(partition by _ order by _) para agrupar los ID de usuario y ordenarlos según el tiempo de inicio de sesión.
3. Calcula la fecha de inicio de sesión menos el valor del resultado obtenido en el segundo paso. Si el usuario inicia sesión continuamente, el resultado de cada resta será el mismo.
4. Agrupa y suma según ID y fecha, filtra los usuarios mayores o iguales a 7 durante 7 días consecutivos.
La información de la tabla se muestra a continuación
Paso 1: Eliminar fechas de inicio de sesión de usuario duplicadas
1select?DISTINCT?date(date)?as?date,id ?de ?orde;
El resultado es:
Paso 2: Utilice la función row_number() sobre() para contar
1select?*, row_number() over(PARTITION ?by?id?order?by?date)?as?cum?from?(select?DISTINCT?date(date)?as?date,id?from?orde)a;
El resultado es:
Paso 3: Resta el valor del recuento de la fecha para obtener el resultado
1select?*, date(date)-cum?as?result?from?( ¿seleccionar?*, número_fila() sobre(PARTICIÓN?por?id?orden?por?fecha)?como?cum?de?(seleccionar?DISTINCT?fecha(fecha)?como?fecha,id?de?orden)a) b;
Resultado:
Paso 4: Agrupar según id y resultado y calcular la suma Los que son mayores o iguales a 7 son usuarios que han iniciado sesión durante 7 consecutivos. días
1seleccionar?id, recuento(* )?desde?(seleccionar?*, fecha(fecha)-cum?como?resultado?de?(seleccionar?*, número_fila() sobre(PARTICIÓN?por ?id?order?by?date)?as?cum?from?(seleccione?DISTINCT?date(date)?as?date,id?from?orde)a)b)c?GROUP?BY?id, resultado? have?count(*)gt;=7;
El resultado es:
Después de usar múltiples consultas anidadas, finalmente obtuvimos los resultados que necesitábamos.
01
01. Restaurar el escenario de la pregunta - crear tabla
select*fromtmp.tmp_last_3_day?
02
02. La clave para resolver el problema - análisis
03
03. El momento de presenciar el milagro - implementación
¿ID de usuario que iniciaron sesión? durante 3 días consecutivos El sql es el siguiente:
Echemos un vistazo a los resultados:
04
04. Resumen de reflexión de la entrevista - sublimación
p>Ahora parece que no es difícil ¡Pues! Las ideas son muy importantes. Con ideas los problemas se resolverán de forma natural (aplausos). . PD: La Sexta Hermana Menor dijo que el plato de esta noche se servirá con muslos de pollo ~ jaja
05
05. Todos los caminos conducen a Roma
En realidad, hay De muchas maneras, las anteriores son solo las seleccionadas. Una más técnica.
El siguiente también es un buen método. Los grandes deben tener otras formas de lograrlo. Los amigos pueden hacer clic en [Ver] en la esquina inferior derecha para dejar un comentario, discutir juntos y progresar juntos ~ vaya, vaya. .
2 preguntas de la entrevista Didi
Tabla de estudiantes: tb_student (nombre: nombre del estudiante, id: número de estudiante, clase: clase, in_time: hora de inscripción, edad: edad, sexo: género , especialización: especialización)
Tabla de puntuación de estudiantes: tb_score (id: número de estudiante, curso: curso, puntuación: puntuación)
(1) Seleccione los 10 estudiantes más jóvenes con especialización en "Computación Ciencias" que se matricularon en 2017 Lista de compañeros (nombre, número de estudiante, clase, edad)
(2) Cuente el número y la proporción de estudiantes en cada clase cuyo puntaje promedio en cada materia sea mayor a 80 puntos
seleccione una .clase,
recuento (caso cuando a.avg_scoregt; 80 y luego 1 extremo nulo) como 'número de personas'
recuento (caso cuando a.avg_scoregt; 80 entonces 1 else null end )/count(a.id) como 'proporción de personas'
de
(seleccione st.class, sc.id, avg(sc.score) como avg_score
de tb_student st
unión interna tb_score sc
en st.id=sc.id
agrupar por 1)a
(1)seleccione st.name, st.id, st.class, st.age
de tb_student st
interno Únase a tb_score sc
en st.id=sc.id
donde año(st.in_time)='2017'
y major='computer'
ordenar por edad asc
límite 0, 9
(2)
Enlace: /p/110924684
(1)
selectname, id, class, agefromtb_studentwhereyear(in_time)=2017andmajor='Computer' ORDER BYageASClimit10;
(2)
SELECTa.class , sum(casewhenaaa.xgt; 80then1else0end)asnum_80, ( sum(casewhenaaa.xgt; 80then1else0end)/count(*))asproportionfromtb_studentaINNERJOIN(SELECTid,avg(score)asxfromtb_scoreGROUPBYid)asaaaona.id=aaa.idGROUPBYclass;
Análisis: escriba una subconsulta de la tabla de puntuación. Obtenga la puntuación promedio de cada materia para los estudiantes agrupados por número de estudiante. Nómbralo aaa y únelo con la mesa de estudiantes. Luego agrupe por clase para obtener el puntaje promedio de los estudiantes en cada clase agrupado por clase. Finalmente, use la declaración de caso y la función de agregación de suma para obtener el puntaje promedio gt y la proporción del número total. de estudiantes en cada clase.
La declaración case también se puede reemplazar por una declaración if
Método de escritura 2:
SELECTa.class, count(if(aaa.avggt; 80, verdadero, nulo))asnumover80,count(if(aaa.avggt; 80, verdadero, nulo))/count(a.id)astotalfromtb_studentaINNERJOIN(SELECTid,avg(score)asxfromtb_scoreGROUPBYid)asaaaona.id=aaa.idGROUPBYclass;