Для авиакомпаний, самолеты которой выполнили хотя бы один рейс, вычислить с точностью до двух десятичных знаков средние величины времени нахождения самолетов в воздухе (в минутах). Также рассчитать указанные характеристики по всем летавшим самолетам (использовать слово 'TOTAL').
Вывод: компания, среднее арифметическое, среднее геометрическое, среднее квадратичное, среднее гармоническое.
With t as
(Select ID_comp, convert(numeric(18,2), Case when time_in > = time_out
Then datediff(minute, time_out, time_in)
Else datediff(minute, time_out, dateadd(day, 1, time_in))
End) as trmin
From (Select trip_no
From Pass_in_trip
Group by trip_no, [date]) pt join Trip t on pt.trip_no = t.trip_no
)
Select Coalesce(c.name, 'TOTAL'), A_mean, G_mean, Q_mean, H_mean
From (
Select Id_comp ,
convert(numeric(18,2), avg(trmin)) A_mean,
convert(numeric(18,2), Exp(avg(Log(trmin)))) G_mean,
convert(numeric(18,2), sqrt(avg(trmin*trmin))) Q_mean,
convert(numeric(18,2), count(*)/sum(1/trmin)) H_mean
From t
Group by ID_comp
with cube) as a left join Company c on a.ID_comp = c.ID_comp
Сгруппировать все окраски по дням, месяцам и годам. Идентификатор каждой группы должен иметь вид "yyyy" для года, "yyyy-mm" для месяца и "yyyy-mm-dd" для дня. Вывести только те группы, в которых количество различных моментов времени (b_datetime), когда выполнялась окраска, более 10. Вывод: идентификатор группы, суммарное количество потраченной краски.
select to_char(trunc(b.b_datetime,'year'),'yyyy') grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,'year'),'yyyy')
having count(distinct b.b_datetime) > 10
union
select to_char(trunc(b.b_datetime,'MM'),'yyyy-mm') grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,'MM'),'yyyy-mm')
having count(distinct b.b_datetime) > 10
union
select to_char(trunc(b.b_datetime,'dd'),'yyyy-mm-dd') grp, sum(b.b_vol) qnt
from utB b
group by to_char(trunc(b.b_datetime,'dd'),'yyyy-mm-dd')
having count(distinct b.b_datetime) > 10
Выборы Директора музея ПФАН проводятся только в високосный год, в первый вторник апреля после первого понедельника апреля. Для каждой даты из таблицы Battles определить дату ближайших (после этой даты) выборов Директора музея ПФАН. Вывод: сражение, дата сражения, дата выборов. Даты выводить в формате "yyyy-mm-dd".
Select name, convert(char(10),date,120) as battle_dt
,convert(char(10),MIN(Dateadd(dd,1,dt)),120) as election_dt
From
(Select name, date, Dateadd(yy,p,Dateadd(dd,n,Dateadd(mm,3,dateadd(yy,datediff(yy,0,date),0)))) as dt
From Battles
,(values(0),(1),(2),(3),(4),(5),(6),(7),(8)) T(p)
,(values(0),(1),(2),(3),(4),(5),(6)) W(n) ) X
Where date<=dt and (Year(dt)%4=0 and Year(dt)%100> 0 or Year(dt)%400=0)
and DATEPART(dw,dt)=DATEPART(dw,'20140106')
GROUP BY name, date
Вывод в три столбца: - страна; - максимальное значение; - слово `numguns` - если максимум достигается для numguns*5000, слово `bore` - если максимум достигается для bore*3000, слово `displacement` - если максимум достигается для displacement. Замечание. Если максимум достигается для нескольких выражений, выводить каждое из них отдельной строкой.
Select top 1 with ties country, x, n
from classes
cross apply(values(numguns*5000,'numguns')
,(bore*3000,'bore')
,(displacement,'displacement'))V(x,n)
group by country, x, n
order by rank()over(partition by country order by x desc)
Считая, что каждая окраска длится ровно секунду, определить непрерывные интервалы времени с длительностью более 1 секунды из таблицы utB. Вывод: дата первой окраски в интервале, дата последней окраски в интервале.
SELECT MIN(D)start, MAX(D)finish
FROM
(
SELECT D, SUM(F)OVER(ORDER BY D ROWS UNBOUNDED PRECEDING)F
FROM
(
SELECT B_DATETIME D, IIF(IsNull(DATEDIFF(second, LAG(B_DATETIME)OVER(ORDER BY B_DATETIME), B_DATETIME),0)<=1,0,1)F
FROM utB
)q
)q
GROUP BY F
HAVING DATEDIFF(second,MIN(D),MAX(D))> 0
Рассмотрим равнобочные трапеции, в каждую из которых можно вписать касающуюся всех сторон окружность. Кроме того, каждая сторона имеет целочисленную длину из множества значений b_vol. Вывести результат в 4 колонки: Up, Down, Side, Rad. Здесь Up - меньшее основание, Down - большее основание, Side - длины боковых сторон, Rad – радиус вписанной окружности (с 2-мя знаками после запятой).
select distinct Up=u.b_vol, Down=d.b_vol, Side=s.b_vol,
Rad=cast(POWER((POWER(s.b_vol,2)-POWER((1.*d.b_vol-1.*u.b_vol)/2,2)),1./2.)/2 as dec(15,2))
from utB u, utB d, utB s
where u.b_vol<d.b_vol and 1.*u.b_vol+1.*d.b_vol=2.*s.b_vol
Вывод: имя и количество полетов на одном и том же месте.
WITH b AS
(SELECT ID_psg, COUNT(*) as cnt FROM Pass_In_Trip GROUP BY ID_psg, place),
b1 AS
(SELECT DISTINCT ID_psg, cnt FROM b WHERE cnt =(SELECT MAX(cnt) FROM b))
SELECT name, cnt FROM b1 JOIN Passenger p ON (b1.ID_psg = p.ID_psg)
Вывод: количество каждой краски в порядке (R,G,B)
SELECT sum(255-ISNULL ([R],0) ) R , sum(255-isnull([G],0)) G, sum(255-isnull([B],0)) B
FROM
(
/*merging all tables to find paint filling and color for all squares*/
select ISNULL(B_Q_ID, Q_ID) ID, V_COLOR, B_VOL Vol from
utB RIGHT JOIN utQ on B_Q_ID=Q_ID
LEFT JOIN utV on B_V_ID=V_ID
) as SourceT
PIVOT
(
/*rotating table and calculating each paint volume for each square*/
SUM(Vol) For V_COLOR IN ([R], [G], [B])
) Pvt
/*excluding white squares*/
where ISNULL ([R],0) + isnull([G],0) + isnull([B],0) <765
select min(Qty) from (select SUM(RemainPaint)/255 Qty FROM (select V_COLOR, V_ID,
CASE
WHEN SUM(B_VOL) IS NULL
THEN 255
ELSE 255-SUM(B_VOL)
END RemainPaint
from utB right join utV on B_V_ID = V_ID
group by V_COLOR, V_ID
) R
group by V_COLOR
) Q
Найти НЕ белые и НЕ черные квадраты, которые окрашены разными цветами в пропорции 1:1:1. Вывод: имя квадрата, количество краски одного цвета
select B_Q_ID, sum(vol)/3 vol
from
(select B_Q_ID, V_COLOR, sum(B_VOL) vol
from utB, utV
where B_V_ID=V_ID
group by B_Q_ID, V_COLOR
) z
group by B_Q_ID
having count(v_color)=3
and sum(vol)<765
and sum(vol) % 3=0
