SQL using a function to check existence of event over a user id?-CodePudding

I have a table 'EVENTS' with a user column, and an 'event' column

User	Event
1	a
1	a
1	a
1	b
2	b
2	c

In the above example, user 1 has never had event c appear for them. I want to do something like

WITH table_a as (
SELECT 
CASE WHEN EVENT = 'c' Then 'Y' ELSE 'n' end as event_occured,
user_id
FROM EVENTS)

and then get a result such as

User	is_occured
1	n
2	y

So I first tried to do it like such

SELECT DISTINCT USER,'y' is_occured FROM table_a WHERE event_occured='y'
UNION 
SELECT DISTINCT USER,'n' is_occured FROM table_a WHERE event_occured='n'

But this is obviously a bit clunky, and will be unmanageable, especially as more columns are added to the event table, and needed in the query. so next I tried to do it using a window function, but I'm not certain how to pull the values into only singular users, where I'm only looking for the existence.

SELECT user,
CASE WHEN ... over(partion by user)
FROM EVENTS

But I'm very confused how to procede or if this is even the right track

CodePudding user response：

If you are purely trying to get a Y or N onto these, you can do a simple MAX with a case expression:

select [User]
    , MAX(case when [Event] = 'c' then 'Y' else 'N' end) is_occurred
from [EVENTS]
group by [User]

If you wanted to avoid group by, you could do a window function:

select distinct [User]
    , MAX(case when [Event] = 'c' then 'Y' else 'N' end) over (partition by [User])
from [EVENTS]

If you wanted to have this as a function, you could parameterize the [Event] comparison and pass the user as well to something like:

select MAX(case when [Event] = @p_checked_event then 'Y' else 'N' end)
from [EVENTS]
where [User] = @p_checked_user

Return the results of that query, and call it like:

select distinct [User]
    , CheckEventOccurred([User], 'c')
from [EVENTS]