Consultas a la Base de Datos MetroBilbao

1 06 2008

He estado practicando un poco de SQL con nuestra base de datos, y voy a poneros aquí las consultas que he hecho para compartirlas con vosotros y, ya de paso, si a alguien se le ocurriesen más posibles consultas, que las postee.
Aviso que en algunas de ellas puede ser que me halla complicado la vida, pero bueno, yo lo veía así y así lo he hecho.

BDMetroBilbao: Consultas estadísticas

(versión no completa)

A continuación, algunos ejemplos:

1.-Mostrar los usuarios dados de alta

SELECT * FROM habitual;

2.-Mostrar los accesos entre 2 fechas

SELECT * FROM Acceso WHERE Fecha BETWEEN '23-02-2008' AND '23-12-2008';

3.-Mostrar los 10 usuarios habituales que más han gastado en acceder al metro. Esto es lo
mismo que si te pido que me muestres los 10 usuarios habituales que más veces han accedido al metro

SELECT * FROM Habitual
WHERE idHabitual = ANY
(SELECT idHabitual FROM Acceso
GROUP BY idHabitual
ORDER BY COUNT(idHabitual) DESC)
LIMIT 10;

3.1-Lo mismo de antes pero mostrando tambien lo que han gastado

SELECT Habitual.idHabitual,Nombre,Apellido,DNI,SUM(Precio) AS Gastado
FROM Habitual
INNER JOIN Acceso ON Acceso.idHabitual=Habitual.idHabitual
INNER JOIN Torno ON Acceso.idTorno=Torno.idTorno
INNER JOIN Linea ON Torno.idLinea=Linea.idLinea
GROUP BY Acceso.idHabitual
ORDER BY Gastado DESC
LIMIT 10;

4.-Mostrar los 10 tornos más usados (idTorno, idEstacion, idLinea) y la cantidad de accesos por los mismos

SELECT torno.idTorno,torno.idLinea,torno.idEstacion,COUNT(acceso.idTorno) AS NumAccesos
FROM Acceso
INNER JOIN Torno ON Torno.idTorno=Acceso.idTorno
GROUP BY acceso.idTorno
ORDER BY NumAccesos DESC
LIMIT 10;

5.-Mostrar la estación con más tráfico: el número medio de accesos diarios en ella

SELECT Nombre,mediaAccesosEstacionFecha FROM
(SELECT Nombre,AVG(NumAccesosEstacionFecha) AS mediaAccesosEstacionFecha FROM
(SELECT Nombre, COUNT(idEntrada) AS NumAccesosEstacionFecha,Fecha
FROM Estacion
INNER JOIN Torno ON Torno.idEstacion=Estacion.idEstacion
INNER JOIN Acceso ON Torno.idTorno=Acceso.idTorno
GROUP BY Fecha,Nombre) T1
GROUP BY Nombre) T2
ORDER BY mediaAccesosEstacionFecha DESC
LIMIT 1;

6.-Mostrar el número medio de accesos diarios en cada estacion

SELECT Nombre,AVG(NumAccesosEstacionFecha) AS mediaAccesosEstacionFecha FROM
(SELECT Nombre, COUNT(idEntrada) AS NumAccesosEstacionFecha,Fecha
FROM Estacion
INNER JOIN Torno ON Torno.idEstacion=Estacion.idEstacion
INNER JOIN Acceso ON Torno.idTorno=Acceso.idTorno
GROUP BY Fecha,Nombre) T1
GROUP BY Nombre;

7.-Mostrar la hora punta de cada estación: la hora en la que el número de accesos es mayor

SELECT Nombre,HoraASecas AS 'Hora Punta',avgAccesosHora AS 'Numero de Accesos' FROM
(SELECT Nombre, HoraASecas,AVG(numAccesosHoraFecha) AS avgAccesosHora
FROM
(SELECT Nombre,HOUR(Hora) AS HoraASecas,Fecha,COUNT(idEntrada) numAccesosHoraFecha
FROM Acceso
INNER JOIN Torno ON Acceso.idTorno=Torno.idTorno
INNER JOIN Estacion ON Torno.idEstacion=Estacion.idEstacion
GROUP BY Nombre,Fecha,HoraASecas) AccesosHoraFechaEstacion
GROUP BY HoraASecas,Nombre) AvgAccesosHoraEstacion
WHERE avgAccesosHora= ANY
(SELECT MAX(avgAccesosHora) FROM
(SELECT Nombre, HoraASecas,AVG(numAccesosHoraFecha) AS avgAccesosHora
FROM
(SELECT Nombre,HOUR(Hora) AS HoraASecas,Fecha,COUNT(idEntrada) numAccesosHoraFecha
FROM Acceso
INNER JOIN Torno ON Acceso.idTorno=Torno.idTorno
INNER JOIN Estacion ON Torno.idEstacion=Estacion.idEstacion
GROUP BY Nombre,Fecha,HoraASecas) AccesosHoraFechaEstacion
GROUP BY HoraASecas,Nombre) AccesosHoraEstacion
GROUP BY Nombre);

8.-Que creando vistas queda así:

8.1.-Crear vista ‘AccesosHoraFechaEstacion’ (Accesos por hora y fecha en cada estación):

CREATE VIEW AccesosHoraFechaEstacion AS
SELECT Nombre,HOUR(Hora) AS HoraASecas,Fecha,COUNT(idEntrada) numAccesosHoraFecha
FROM Acceso
INNER JOIN Torno ON Acceso.idTorno=Torno.idTorno
INNER JOIN Estacion ON Torno.idEstacion=Estacion.idEstacion
GROUP BY Nombre,Fecha,HoraASecas;

8.2.-Crear vista ‘AvgAccesosHoraEstacion’ (Media de accesos por hora en cada estación):

CREATE VIEW AvgAccesosHoraEstacion AS
SELECT Nombre, HoraASecas,AVG(numAccesosHoraFecha) AS avgAccesosHora
FROM AccesosHoraFechaEstacion
GROUP BY HoraASecas,Nombre;

8.3.-Consulta ‘Hora Punta’ de cada estación (u horas punta) usando las vistas anteriores:

SELECT Nombre,HoraASecas AS 'Hora Punta',avgAccesosHora
FROM avgAccesosHoraEstacion
WHERE avgAccesosHora= ANY (SELECT MAX(avgAccesosHora) FROM avgAccesosHoraEstacion GROUP BY Nombre);


Acciones

Information

4 responses

2 06 2008
Daniel García

He corregido lo de la hora punta, que estaba mal :S

2 06 2008
ionbixen

joder tio pon los numeros bien… ya he hecho las 4 primeras :D soy guay

3 06 2008
Daniel García

Consultas que se harán en el programa de gestión:

METRO, GLOBAL
Los acceso al metro entre dos horas,dos fechas…
El total de accesos de habituales y esporádicos durante un día.
Día de mayor afluencia de usuarios.
Ordenar los meses según el uso de usuarios
Hora punta del servicio, total de usuarios (listado de dias)
Media de acceso por trimestres, años.

USUARIOS
Los 100 usuarios que mas usan el metro
Usuarios que nunca ha tenido un acceso
Usuarios que no dispone de dinero
Ver datos de usuarios habituales por localidades..
Usuarios que en un periodo de tiempo no usa el servicio

LINEA
El total de accesos por línea por día.
El total de accesos por línea.
Linea que mas se usa.

ESTACION
El total de accesos por estación por día.
El total de accesos por estación.
La estación que dispone de mas accesos durante un día
Estadísticas totales de una estación durante un día, usuarios, recargas, accesos:
Mostrar el número medio de accesos diarios en cada estacion
Las lineas que mas se usan en cada estación, en el caso de tener dos lineas.
Estacion mas usada
Mostrar la hora punta de cada estación: la hora en la que el número de accesos es mayor

3 06 2008
ionbixen

yo creo que la de la hora punta deberia ser asi:
select nombre,horita,fecha,max(mediaAccesos)
from (select nombre,fecha,horita,avg(accesos) as mediaAccesos,fecha as mediaEntrada
from (select hour(hora) as horita,nombre,fecha,count(idEntrada) as Accesos
from estacion inner join torno
on torno.idEstacion=Estacion.idEstacion
inner join acceso
on acceso.idTorno=Torno.idTorno
group by fecha,nombre,hour(hora)) T1
group by horita,nombre) T2
group by nombre,horita;

pero no se porke no funciona el max de arriba que deberia seleccionar las mas altas pero weno…

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s




A %d blogueros les gusta esto: