Red de conocimiento informático - Problemas con los teléfonos móviles - preguntas de la entrevista sql 1

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;