Т-SQL для пробоя Ашераденс информацию


У меня есть таблица, которая содержит значения UserAgent строку и посчитать , сколько раз его видел. Т-SQL ниже используется, чтобы дать разбивку каких браузерах не видно и как часто потом видел.

Я ищу комментарии (хорошие или плохие) и предложения (хорошие только).

SELECT
Browser,
LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version + '.0') + 1) - 1) AS Version,
SUM(Count) AS Count
FROM
(
SELECT
CASE
    WHEN UserAgent LIKE '%Firefox/%' THEN 'Firefox'
    WHEN UserAgent LIKE '%Chrome/%' THEN 'Chrome'
    WHEN UserAgent LIKE '%MSIE %' THEN 'IE'
    WHEN UserAgent LIKE '%MSIE+%' THEN 'IE'
    WHEN UserAgent LIKE '%iPhone%' THEN 'iPhone Safari'
    WHEN UserAgent LIKE '%iPad%' THEN 'iPad Safari'
    WHEN UserAgent LIKE '%Opera%' THEN 'Opera'
    WHEN UserAgent LIKE '%BlackBerry%' AND UserAgent LIKE '%Version/%' THEN 'BlackBerry WebKit'
    WHEN UserAgent LIKE '%BlackBerry%' THEN 'BlackBerry'
    WHEN UserAgent LIKE '%Android%' THEN 'Android'
    WHEN UserAgent LIKE '%Safari%' THEN 'Safari'
    WHEN UserAgent LIKE '%bot%' THEN 'Bot'
    WHEN UserAgent LIKE '%http://%' THEN 'Bot'
    WHEN UserAgent LIKE '%www.%' THEN 'Bot'
    WHEN UserAgent LIKE '%Wget%' THEN 'Bot'
    WHEN UserAgent LIKE '%curl%' THEN 'Bot'
    WHEN UserAgent LIKE '%urllib%' THEN 'Bot'
    ELSE 'Unknown'
END AS Browser,
CASE
    WHEN UserAgent LIKE '%Firefox/%' THEN LEFT(RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6), CHARINDEX(' ', RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6)) - 1)
    WHEN UserAgent LIKE '%Chrome/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6)) - 1)
    WHEN UserAgent LIKE '%MSIE %' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', UserAgent + ';') - 4), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', UserAgent + ';') - 4)) - 1)
    WHEN UserAgent LIKE '%MSIE+%' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE+', UserAgent + ';') - 4), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE+', UserAgent + ';') - 4)) - 1)
    WHEN UserAgent LIKE '%iPhone%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
    WHEN UserAgent LIKE '%iPad%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
    WHEN UserAgent LIKE '%Opera%' THEN LEFT(RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Opera/', UserAgent + ' ') - 4), CHARINDEX(' ', RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Opera/', UserAgent + ' ') - 4)) - 1)
    WHEN UserAgent LIKE '%BlackBerry%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
    WHEN UserAgent LIKE '%BlackBerry%' THEN RIGHT(LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1), LEN(LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1)) - CHARINDEX('/', LEFT(UserAgent + ' ', CHARINDEX(' ', UserAgent + ' ') - 1)))
    WHEN UserAgent LIKE '%Android%' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('Android ', UserAgent + ';') - 7), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('Android ', UserAgent + ';') - 7)) - 1)
    WHEN UserAgent LIKE '%Safari%' AND UserAgent LIKE '%Version/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Version/', UserAgent) - 7)) - 1)
    WHEN UserAgent LIKE '%bot%' THEN '0.0'
    WHEN UserAgent LIKE '%http://%' THEN '0.0'
    WHEN UserAgent LIKE '%www.%' THEN '0.0'
    WHEN UserAgent LIKE '%Wget%' THEN '0.0'
    WHEN UserAgent LIKE '%curl%' THEN '0.0'
    WHEN UserAgent LIKE '%urllib%' THEN '0.0'
    ELSE '0.0'
END AS Version,
Count
FROM AnalyticsBrowsers
) as Browsers
WHERE
Browser NOT LIKE 'Bot'
GROUP BY
Browser, LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version + '.0') + 1) - 1)
ORDER BY Count DESC

Выход из данных видео сайте собраны 8/25/2011 в 9/7/2011:

Browser           Version      Count
IE                8.0          1495
IE                7.0          659
IE                6.0          470
Chrome            13.0         354
Firefox           6.0          345
IE                9.0          252
Firefox           3.6          213
Firefox           5.0          156
iPhone Safari     5.0          110
Safari            5.0          98
Safari            5.1          77
Unknown           0.0          47
iPad Safari       5.0          41
Firefox           4.0          34
Android           2.2          31
Firefox           3.5          16
Firefox           3.0          15
Chrome            14.0         11
Opera             9.80         11
Chrome            11.0         10
iPhone Safari     4.0          10
Android           2.3          8
iPhone Safari     0.0          7
Safari            4.0          7
Firefox           2.0          5
Android           2.1-update1  4
Firefox           1.5          4
Chrome            10.0         4
BlackBerry        4.5          4
BlackBerry        5.0          4
IE                5.5          4
Opera             9.24         4
Firefox           7.0          4
Chrome            9.0          3
BlackBerry        4.7          3
Safari            4.1          3
iPad Safari       4.0          3
Chrome            15.0         3
Safari            3.0          2
Safari            0.0          2
BlackBerry WebKit 6.0          2
iPhone Safari     5.1          2
Firefox           8.0a2        2
IE                5.01         1
Chrome            5.0          1
Android           1.5          1
Chrome            12.0         1
Safari            3.2          1


3504
11
задан 7 сентября 2011 в 10:09 Источник Поделиться
Комментарии
4 ответа

Вы можете устранить несколько условий путем их объединения. Ваши две проверки для IE в "браузере" в разделе могут быть объединены в один , когда юзерагент как '%несовременно%' , так как вы на самом деле не доклад тем, как разные сущности. Кроме того, ваша "версия" не нуждается в явных проверок для всех бот агентов, так как вы просто отчет 0.0 для всех из них. Вы можете просто позволить этому падать на другой пункт.

4
ответ дан 9 сентября 2011 в 06:09 Источник Поделиться

Не полный ответ, но я бы лично попробовать, чтобы хранить все сопоставления вы используете в "случае" заявление на стол.
Так что вы могли бы заменить подзапрос с учетом всех этих случаях присоединяться.

5
ответ дан 9 сентября 2011 в 05:09 Источник Поделиться

Просто первое впечатление...не критиковать...

Лично я бы рекомендовал нормализовать свой стол AnalyticBrowsers просто немного больше... отдельные столбцы для браузера и версии как минимум.

Это не только сделает вашу функцию-тяжелых / сложных SQL легче писать и процесс...

SELECT
Browser,
Version,
SUM(Count) AS Count
FROM
AnalyticsBrowsers
WHERE
Browser NOT LIKE 'Bot'
GROUP BY
Browser,
Version
ORDER BY
Count DESC

Но это будет также сделать ваши данные более масштабируемым, в случае, если вы когда-нибудь в будущем хотите сохранить данные в новые таблицы, которая может затронуть 'в Firefox' и т. д.

Что касается содержания...данные из такого рода запросов не много значат без даты контексте. Я думаю, что вы по этому поводу заметил, но это не в SQL, так что чистки рядов.

Разбираем Ашераденс значение перед вставкой в таблицу AnalyticBrowsers. Веб-страница может сделать это, или хранимую процедуру в базе данных, строить его в код вставить синтаксис и т. д.

2
ответ дан 14 сентября 2011 в 04:09 Источник Поделиться

Имейте в виду, что лен функция не учитывает концевые пробелы. Это означает, что лен(юзерагент + ' ') фактически равна лен(юзерагент). Связанные так Вопрос/ответ предлагает использовать DATALENGTH вместо (или DATALENGTH(х)/2 для Unicode строк). Это поможет будущим разработчиков, которым нужно поддерживать свой код и не можете быть знакомы с конкретным поведением лен функцию.

1
ответ дан 10 сентября 2011 в 05:09 Источник Поделиться