none
T-SQL: подсчет подписчиков RRS feed

  • Вопрос

  • Здравствуйте! Прошу помощи:

    Имеется таблица с пользователями-подписчиками, состоящая из [usr_id, subscriptions, usr_name], где subsctiptions - varchar[max] из ID публикаторов '1,2,3,8'.
    Необходимо для каждого публикатора, определить, сколько у него подписчиков, таблица вида [publisher_id, sub_count, usr_names], где sub_count - количество подписок, usr_names - перечисление всех имен подписавшихся.
    Как наиболее правильно реализовать? Спасибо.
    16 октября 2012 г. 9:00

Ответы

  • Использовать нормализированные справочники

    Нет дело не в нормализации. Мне необходимо произвести разовый подсчет, если бы хотя бы пример кода, был бы признателен.

    declare @t table ( usr_id int, subscriptions varchar(100), usr_name varchar(100) )
    insert into @t
    values ( 1, '1,2,3,8', 'Вася' )
    , ( 2, '5,6', 'Петя' )
    , ( 3, '2,3,4,5', 'Ваня' )
    , ( 4, '2,8', 'Ещё какой-то чудик' );
    
    
    with cte
    as
    (
      select a.b.value( '.', 'varchar(10)' ) publisher_id, usr_name 
        from (select usr_name, cast( '<pid>' + replace( subscriptions, ',', '</pid><pid>' ) + '</pid>' as xml ) x from @t
             ) t
        cross apply t.x.nodes( './pid' ) a(b)
    )
    select publisher_id
         , count(*) sub_count
         , stuff( ( select ', ' + usr_name 
                      from cte where publisher_id = t.publisher_id for xml path('') 
                  ), 1, 2, ''
                ) as usr_names
      from cte t
      group by publisher_id


    http://www.t-sql.ru


    • Изменено Alexey KnyazevModerator 16 октября 2012 г. 9:17
    • Помечено в качестве ответа mi4ael 16 октября 2012 г. 10:02
    16 октября 2012 г. 9:16
    Модератор

Все ответы

  • Как наиболее правильно реализовать? Спасибо.

    Использовать нормализированные справочники

    http://www.t-sql.ru

    16 октября 2012 г. 9:04
    Модератор
  • Использовать нормализированные справочники

    Нет дело не в нормализации. Мне необходимо произвести разовый подсчет, если бы хотя бы пример кода, был бы признателен.
    16 октября 2012 г. 9:13
  • Использовать нормализированные справочники

    Нет дело не в нормализации. Мне необходимо произвести разовый подсчет, если бы хотя бы пример кода, был бы признателен.

    declare @t table ( usr_id int, subscriptions varchar(100), usr_name varchar(100) )
    insert into @t
    values ( 1, '1,2,3,8', 'Вася' )
    , ( 2, '5,6', 'Петя' )
    , ( 3, '2,3,4,5', 'Ваня' )
    , ( 4, '2,8', 'Ещё какой-то чудик' );
    
    
    with cte
    as
    (
      select a.b.value( '.', 'varchar(10)' ) publisher_id, usr_name 
        from (select usr_name, cast( '<pid>' + replace( subscriptions, ',', '</pid><pid>' ) + '</pid>' as xml ) x from @t
             ) t
        cross apply t.x.nodes( './pid' ) a(b)
    )
    select publisher_id
         , count(*) sub_count
         , stuff( ( select ', ' + usr_name 
                      from cte where publisher_id = t.publisher_id for xml path('') 
                  ), 1, 2, ''
                ) as usr_names
      from cte t
      group by publisher_id


    http://www.t-sql.ru


    • Изменено Alexey KnyazevModerator 16 октября 2012 г. 9:17
    • Помечено в качестве ответа mi4ael 16 октября 2012 г. 10:02
    16 октября 2012 г. 9:16
    Модератор
  • Спасибо, то что надо :)

    16 октября 2012 г. 9:26
  • Спасибо, то что надо :)


    А чего тогда ответ не пометили? ;)

    http://www.t-sql.ru

    16 октября 2012 г. 9:27
    Модератор
  • Можно ещё коротко объяснить, что мы здесь сделали?
    17 октября 2012 г. 7:42
  • Можно ещё коротко объяснить, что мы здесь сделали?

    1) нормализовали - распарсили строку по разделителю ","

    2) сделали группировку по этому распарсенному полю, а затем опять денормализовали (склеили в строку) имена юзеров


    http://www.t-sql.ru

    17 октября 2012 г. 7:53
    Модератор
  • громоздко получилось, не могу разобраться как это слить с таблицей публикаторов, чтобы если у него 0 подписчиков то был 0.
    17 октября 2012 г. 8:20
  • громоздко получилось, не могу разобраться как это слить с таблицей публикаторов, чтобы если у него 0 подписчиков то был 0.

    если таблица публикаторов существует, то просто объедините результат запроса и выборки из таблицы

    http://www.t-sql.ru

    17 октября 2012 г. 8:48
    Модератор
  • Да, сделал, работает. Вот только при вставку в Jobs пишет ошибку:

    INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934)

    перед select вставил insert into tb_publishers_subscribers

    17 октября 2012 г. 10:37
  • Ну так установите нужное значение опции QUOTED_IDENTIFIER
    17 октября 2012 г. 10:53
  • Да, сделал, работает. Вот только при вставку в Jobs пишет ошибку:

    INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934)

    перед select вставил insert into tb_publishers_subscribers

    http://t-sql.ru/post/QUOTED_IDENTIFIER.aspx


    http://www.t-sql.ru

    17 октября 2012 г. 12:08
    Модератор