Count patterns of behaviour

Question

Count patterns of behaviour

OscarBoots

Ten Centuries

Points: 1258
More actions
April 1, 2016 at 2:22 am

#326234

Hi Forum,
I have a large dataset of Peoples Actions over a period of time.
There are about 4 Action steps over the time period these people can choose to do, each Action on different dates.
Example,
Person 1 can 1. go on holiday, 2. restart work, 3. buy a car, 4. get married
Person 2 can 1. go on holiday, 2. emigrate , 3. buy a car, 4. get divorced
I want to count each series of Actions separately but I'm stuck for a method.
So I'd like all people who has done what Person 1 has done & then what Person 2 has done so on.
Has anyone had a similar task to solve?
All suggestions welcome.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I don't know whether this is the most efficient way, but you could use bitmasks. In a separate table, assign a power of two to each action, for example go on holiday =1, restart work = 2, buy a car = 4, get married = 8, emigrate = 16 , get divorced = 32.

Then your query would look something like this:

SELECT Person

FROM PersonActivity p

JOIN Activities a ON p.Person = a.Person

GROUP BY a.Person

HAVING SUM(a.BitCode) = (

SELECT SUM(a1.BitCode)

FROM PersonActivity p1

JOIN Activities a1 ON p1.Person = a1.Person

WHERE p1.Person = 'Person1'

);

John

Edit - corrected error in subquery

OscarBoots Ten Centuries Points: 1258 More actions · Answer 2

Hi John Mitchell,

Thank you!

I've used that logic before but had forgotten about it.

I think you've answered my question, I'll give your code a try.

Thanks again!

Peter

OscarBoots Ten Centuries Points: 1258 More actions · Answer 3

Hi Again John,

I have used prime numbers before to come up with this solution but haven't explored the Bitcode method.

What I mean is I had a task where I named a certain situation as a Prime Number & as long as I didn't go beyond a certain range of Prime Numbers, the sum of these numbers would always be different.

The only problem was there is the possibility of adding Prime numbers will give the same answer for a different combo.

Could you help me with this Bitcode method?

A script maybe that I could run to get an idea of how the Bitcode method works?

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

pbo71465 (4/1/2016)
...as long as I didn't go beyond a certain range of Prime Numbers, the sum of these numbers would always be different.
The only problem was there is the possibility of adding Prime numbers will give the same answer for a different combo.

The product of prime numbers will be unique, not the sum. The sum of powers of two will be unique. The problem with prime numbers is that when you start multiplying them together, you're in danger of overflowing whatever data type you're using.

Not sure what script you're looking for from me - I've already given you a query to use. If you need more specific help, please post DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements.

John

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

pbo71465 (4/1/2016)
Could you help me with this Bitcode method?

Start here: https://msdn.microsoft.com/en-us/library/ms176122.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OscarBoots Ten Centuries Points: 1258 More actions · Answer 6

Thanks John,

I now understand the logic which is the sum total using Bitcode being unique, thanks.

The other requirement is that the Actions must be in the same Order.

Can your code be adapted for this?

Thanks

OscarBoots Ten Centuries Points: 1258 More actions · Answer 7

OscarBoots

Ten Centuries

Points: 1258

April 1, 2016 at 5:14 pm

#1869276

Thanks Jeff.